{
 "metadata": {
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.9-final"
  },
  "orig_nbformat": 2,
  "kernelspec": {
   "name": "pycharm-f7427e7c",
   "display_name": "PyCharm (pythonProject)",
   "language": "python"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2,
 "cells": [
  {
   "source": [
    "<center><h1>参考答案</h1></center>"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt"
   ]
  },
  {
   "source": [
    "## 第一章 预备知识\n",
    "\n",
    "### Ex1：利用列表推导式写矩阵乘法"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "metadata": {},
     "execution_count": 2
    }
   ],
   "source": [
    "M1 = np.random.rand(2,3)\n",
    "M2 = np.random.rand(3,4)\n",
    "res = [[sum([M1[i][k] * M2[k][j] for k in range(M1.shape[1])]) for j in range(M2.shape[1])] for i in range(M1.shape[0])]\n",
    "(np.abs((M1@M2 - res) < 1e-15)).all()"
   ]
  },
  {
   "source": [
    "### Ex2：更新矩阵"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "array([[1.83333333, 3.66666667, 5.5       ],\n",
       "       [2.46666667, 3.08333333, 3.7       ],\n",
       "       [2.65277778, 3.03174603, 3.41071429]])"
      ]
     },
     "metadata": {},
     "execution_count": 3
    }
   ],
   "source": [
    "A = np.arange(1,10).reshape(3,-1)\n",
    "B = A*(1/A).sum(1).reshape(-1,1)\n",
    "B"
   ]
  },
  {
   "source": [
    "### Ex3：卡方统计量"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "11.842696601945802"
      ]
     },
     "metadata": {},
     "execution_count": 4
    }
   ],
   "source": [
    "np.random.seed(0)\n",
    "A = np.random.randint(10, 20, (8, 5))\n",
    "B = A.sum(0)*A.sum(1).reshape(-1, 1)/A.sum()\n",
    "res = ((A-B)**2/B).sum()\n",
    "res"
   ]
  },
  {
   "source": [
    "### Ex4：改进矩阵计算的性能\n",
    "\n",
    "原方法："
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "100566"
      ]
     },
     "metadata": {},
     "execution_count": 5
    }
   ],
   "source": [
    "np.random.seed(0)\n",
    "m, n, p = 100, 80, 50\n",
    "B = np.random.randint(0, 2, (m, p))\n",
    "U = np.random.randint(0, 2, (p, n))\n",
    "Z = np.random.randint(0, 2, (m, n))\n",
    "def solution(B=B, U=U, Z=Z):\n",
    "    L_res = []\n",
    "    for i in range(m):\n",
    "        for j in range(n):\n",
    "            norm_value = ((B[i]-U[:,j])**2).sum()\n",
    "            L_res.append(norm_value*Z[i][j])\n",
    "    return sum(L_res)\n",
    "solution(B, U, Z)"
   ]
  },
  {
   "source": [
    "改进方法：\n",
    "\n",
    "令$Y_{ij} = \\|B_i-U_j\\|_2^2$，则$\\displaystyle R=\\sum_{i=1}^m\\sum_{j=1}^n Y_{ij}Z_{ij}$，这在`Numpy`中可以用逐元素的乘法后求和实现，因此问题转化为了如何构造`Y`矩阵。\n",
    "\n",
    "$$\n",
    "\\begin{split}Y_{ij} &= \\|B_i-U_j\\|_2^2\\\\\n",
    "&=\\sum_{k=1}^p(B_{ik}-U_{kj})^2\\\\\n",
    "&=\\sum_{k=1}^p B_{ik}^2+\\sum_{k=1}^p U_{kj}^2-2\\sum_{k=1}^p B_{ik}U_{kj}\\\\\\end{split}\n",
    "$$\n",
    "\n",
    "从上式可以看出，第一第二项分别为$B$的行平方和与$U$的列平方和，第三项是两倍的内积。因此，$Y$矩阵可以写为三个部分，第一个部分是$m×n$的全$1$矩阵每行乘以$B$对应行的行平方和，第二个部分是相同大小的全$1$矩阵每列乘以$U$对应列的列平方和，第三个部分恰为$B$矩阵与$U$矩阵乘积的两倍。从而结果如下："
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "100566"
      ]
     },
     "metadata": {},
     "execution_count": 6
    }
   ],
   "source": [
    "(((B**2).sum(1).reshape(-1,1) + (U**2).sum(0) - 2*B@U)*Z).sum()"
   ]
  },
  {
   "source": [
    "对比它们的性能："
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "43.1 ms ± 2.38 ms per loop (mean ± std. dev. of 7 runs, 30 loops each)\n"
     ]
    }
   ],
   "source": [
    "%timeit -n 30 solution(B, U, Z)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "697 µs ± 108 µs per loop (mean ± std. dev. of 7 runs, 30 loops each)\n"
     ]
    }
   ],
   "source": [
    "%timeit -n 30 ((np.ones((m,n))*(B**2).sum(1).reshape(-1,1) + np.ones((m,n))*(U**2).sum(0) - 2*B@U)*Z).sum()"
   ]
  },
  {
   "source": [
    "### Ex5：连续整数的最大长度"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "4"
      ]
     },
     "metadata": {},
     "execution_count": 9
    }
   ],
   "source": [
    "f = lambda x:np.diff(np.nonzero(np.r_[1,np.diff(x)!=1,1])).max()\n",
    "f([1,2,5,6,7])\n",
    "f([3,2,1,2,3,4,6])"
   ]
  },
  {
   "source": [
    "## 第二章 pandas基础\n",
    "### Ex1：口袋妖怪数据集\n",
    "#### 1."
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "0.0"
      ]
     },
     "metadata": {},
     "execution_count": 10
    }
   ],
   "source": [
    "df = pd.read_csv('../data/pokemon.csv')\n",
    "(df[['HP', 'Attack', 'Defense', 'Sp. Atk', 'Sp. Def', 'Speed']].sum(1)!=df['Total']).mean()"
   ]
  },
  {
   "source": [
    "#### 2.\n",
    "(a)"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "Index(['Water', 'Normal', 'Grass'], dtype='object')"
      ]
     },
     "metadata": {},
     "execution_count": 11
    }
   ],
   "source": [
    "dp_dup = df.drop_duplicates('#', keep='first')\n",
    "dp_dup['Type 1'].nunique()\n",
    "dp_dup['Type 1'].value_counts().index[:3]"
   ]
  },
  {
   "source": [
    "(b)"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "143"
      ]
     },
     "metadata": {},
     "execution_count": 12
    }
   ],
   "source": [
    "attr_dup = dp_dup.drop_duplicates(['Type 1', 'Type 2'])\n",
    "attr_dup.shape[0]"
   ]
  },
  {
   "source": [
    "(c)"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "170"
      ]
     },
     "metadata": {},
     "execution_count": 13
    }
   ],
   "source": [
    "L_full = [i+' '+j if i!=j else i for i in df['Type 1'].unique() for j in df['Type 1'].unique()]\n",
    "L_part = [i+' '+j if not isinstance(j, float) else i for i, j in zip(df['Type 1'], df['Type 2'])]\n",
    "res = set(L_full).difference(set(L_part))\n",
    "len(res) # 太多，不打印了"
   ]
  },
  {
   "source": [
    "#### 3.\n",
    "(a)"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "0    low\n",
       "1    mid\n",
       "2    mid\n",
       "3    mid\n",
       "4    mid\n",
       "Name: Attack, dtype: object"
      ]
     },
     "metadata": {},
     "execution_count": 14
    }
   ],
   "source": [
    "df['Attack'].mask(df['Attack']>120, 'high').mask(df['Attack']<50, 'low').mask((50<=df['Attack'])&(df['Attack']<=120), 'mid').head()"
   ]
  },
  {
   "source": [
    "(b)"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "0    GRASS\n",
       "1    GRASS\n",
       "2    GRASS\n",
       "3    GRASS\n",
       "4     FIRE\n",
       "Name: Type 1, dtype: object"
      ]
     },
     "metadata": {},
     "execution_count": 15
    }
   ],
   "source": [
    "df['Type 1'].replace({i:str.upper(i) for i in df['Type 1'].unique()})\n",
    "df['Type 1'].apply(lambda x:str.upper(x)).head()"
   ]
  },
  {
   "source": [
    "(c)"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "       #                 Name  Type 1  Type 2  Total   HP  Attack  Defense  \\\n",
       "230  213              Shuckle     Bug    Rock    505   20      10      230   \n",
       "121  113              Chansey  Normal     NaN    450  250       5        5   \n",
       "261  242              Blissey  Normal     NaN    540  255      10       10   \n",
       "333  306    AggronMega Aggron   Steel     NaN    630   70     140      230   \n",
       "224  208  SteelixMega Steelix   Steel  Ground    610   75     125      230   \n",
       "\n",
       "     Sp. Atk  Sp. Def  Speed  Deviation  \n",
       "230       10      230      5      215.0  \n",
       "121       35      105     50      207.5  \n",
       "261       75      135     55      190.0  \n",
       "333       60       80     50      155.0  \n",
       "224       55       95     30      145.0  "
      ],
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>#</th>\n      <th>Name</th>\n      <th>Type 1</th>\n      <th>Type 2</th>\n      <th>Total</th>\n      <th>HP</th>\n      <th>Attack</th>\n      <th>Defense</th>\n      <th>Sp. Atk</th>\n      <th>Sp. Def</th>\n      <th>Speed</th>\n      <th>Deviation</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>230</th>\n      <td>213</td>\n      <td>Shuckle</td>\n      <td>Bug</td>\n      <td>Rock</td>\n      <td>505</td>\n      <td>20</td>\n      <td>10</td>\n      <td>230</td>\n      <td>10</td>\n      <td>230</td>\n      <td>5</td>\n      <td>215.0</td>\n    </tr>\n    <tr>\n      <th>121</th>\n      <td>113</td>\n      <td>Chansey</td>\n      <td>Normal</td>\n      <td>NaN</td>\n      <td>450</td>\n      <td>250</td>\n      <td>5</td>\n      <td>5</td>\n      <td>35</td>\n      <td>105</td>\n      <td>50</td>\n      <td>207.5</td>\n    </tr>\n    <tr>\n      <th>261</th>\n      <td>242</td>\n      <td>Blissey</td>\n      <td>Normal</td>\n      <td>NaN</td>\n      <td>540</td>\n      <td>255</td>\n      <td>10</td>\n      <td>10</td>\n      <td>75</td>\n      <td>135</td>\n      <td>55</td>\n      <td>190.0</td>\n    </tr>\n    <tr>\n      <th>333</th>\n      <td>306</td>\n      <td>AggronMega Aggron</td>\n      <td>Steel</td>\n      <td>NaN</td>\n      <td>630</td>\n      <td>70</td>\n      <td>140</td>\n      <td>230</td>\n      <td>60</td>\n      <td>80</td>\n      <td>50</td>\n      <td>155.0</td>\n    </tr>\n    <tr>\n      <th>224</th>\n      <td>208</td>\n      <td>SteelixMega Steelix</td>\n      <td>Steel</td>\n      <td>Ground</td>\n      <td>610</td>\n      <td>75</td>\n      <td>125</td>\n      <td>230</td>\n      <td>55</td>\n      <td>95</td>\n      <td>30</td>\n      <td>145.0</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 16
    }
   ],
   "source": [
    "df['Deviation'] = df[['HP', 'Attack', 'Defense', 'Sp. Atk', 'Sp. Def', 'Speed']].apply(lambda x:np.max((x-x.median()).abs()), 1)\n",
    "df.sort_values('Deviation', ascending=False).head()"
   ]
  },
  {
   "source": [
    "### Ex2：指数加权窗口\n",
    "#### 1."
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "0   -1.000000\n",
       "1   -1.000000\n",
       "2   -1.409836\n",
       "3   -1.609756\n",
       "4   -1.725845\n",
       "dtype: float64"
      ]
     },
     "metadata": {},
     "execution_count": 17
    }
   ],
   "source": [
    "np.random.seed(0)\n",
    "s = pd.Series(np.random.randint(-1,2,30).cumsum())\n",
    "s.ewm(alpha=0.2).mean().head()\n",
    "def ewm_func(x, alpha=0.2):\n",
    "    win = (1-alpha)**np.arange(x.shape[0])[::-1]\n",
    "    res = (win*x).sum()/win.sum()\n",
    "    return res\n",
    "s.expanding().apply(ewm_func).head()"
   ]
  },
  {
   "source": [
    "#### 2.\n",
    "\n",
    "新的权重为$w_i = (1 - \\alpha)^i, i\\in \\{0,1,...,n-1\\}$，$y_t$更新如下：\n",
    "$$\n",
    "\\begin{split}y_t &=\\frac{\\sum_{i=0}^{n-1} w_i x_{t-i}}{\\sum_{i=0}^{n-1} w_i} \\\\\n",
    "&=\\frac{x_t + (1 - \\alpha)x_{t-1} + (1 - \\alpha)^2 x_{t-2} + ...\n",
    "+ (1 - \\alpha)^{n-1} x_{t-(n-1)}}{1 + (1 - \\alpha) + (1 - \\alpha)^2 + ...\n",
    "+ (1 - \\alpha)^{n-1}}\\\\\\end{split}\n",
    "$$\n"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "0         NaN\n",
       "1         NaN\n",
       "2         NaN\n",
       "3   -1.609756\n",
       "4   -1.826558\n",
       "dtype: float64"
      ]
     },
     "metadata": {},
     "execution_count": 18
    }
   ],
   "source": [
    "s.rolling(window=4).apply(ewm_func).head() # 无需对原函数改动"
   ]
  },
  {
   "source": [
    "## 第三章 索引\n",
    "### Ex1：公司员工数据集\n",
    "#### 1."
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "      EmployeeID birthdate_key  age city_name department     job_title gender\n",
       "3611        5791     1/14/1975   40   Kelowna      Dairy  Dairy Person      M\n",
       "3613        5793     1/22/1975   40  Richmond     Bakery         Baker      M\n",
       "3615        5795     1/30/1975   40   Nanaimo      Dairy  Dairy Person      M"
      ],
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>EmployeeID</th>\n      <th>birthdate_key</th>\n      <th>age</th>\n      <th>city_name</th>\n      <th>department</th>\n      <th>job_title</th>\n      <th>gender</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>3611</th>\n      <td>5791</td>\n      <td>1/14/1975</td>\n      <td>40</td>\n      <td>Kelowna</td>\n      <td>Dairy</td>\n      <td>Dairy Person</td>\n      <td>M</td>\n    </tr>\n    <tr>\n      <th>3613</th>\n      <td>5793</td>\n      <td>1/22/1975</td>\n      <td>40</td>\n      <td>Richmond</td>\n      <td>Bakery</td>\n      <td>Baker</td>\n      <td>M</td>\n    </tr>\n    <tr>\n      <th>3615</th>\n      <td>5795</td>\n      <td>1/30/1975</td>\n      <td>40</td>\n      <td>Nanaimo</td>\n      <td>Dairy</td>\n      <td>Dairy Person</td>\n      <td>M</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 19
    }
   ],
   "source": [
    "df = pd.read_csv('../data/company.csv')\n",
    "dpt = ['Dairy', 'Bakery']\n",
    "df.query(\"(age <= 40)&(department == @dpt)&(gender=='M')\").head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "      EmployeeID birthdate_key  age city_name department     job_title gender\n",
       "3611        5791     1/14/1975   40   Kelowna      Dairy  Dairy Person      M\n",
       "3613        5793     1/22/1975   40  Richmond     Bakery         Baker      M\n",
       "3615        5795     1/30/1975   40   Nanaimo      Dairy  Dairy Person      M"
      ],
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>EmployeeID</th>\n      <th>birthdate_key</th>\n      <th>age</th>\n      <th>city_name</th>\n      <th>department</th>\n      <th>job_title</th>\n      <th>gender</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>3611</th>\n      <td>5791</td>\n      <td>1/14/1975</td>\n      <td>40</td>\n      <td>Kelowna</td>\n      <td>Dairy</td>\n      <td>Dairy Person</td>\n      <td>M</td>\n    </tr>\n    <tr>\n      <th>3613</th>\n      <td>5793</td>\n      <td>1/22/1975</td>\n      <td>40</td>\n      <td>Richmond</td>\n      <td>Bakery</td>\n      <td>Baker</td>\n      <td>M</td>\n    </tr>\n    <tr>\n      <th>3615</th>\n      <td>5795</td>\n      <td>1/30/1975</td>\n      <td>40</td>\n      <td>Nanaimo</td>\n      <td>Dairy</td>\n      <td>Dairy Person</td>\n      <td>M</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 20
    }
   ],
   "source": [
    "df.loc[(df.age<=40)&df.department.isin(dpt)&(df.gender=='M')].head(3)"
   ]
  },
  {
   "source": [
    "#### 2."
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "   EmployeeID  age                      job_title\n",
       "1        1319   58                      VP Stores\n",
       "3        1321   56             VP Human Resources\n",
       "5        1323   53      Exec Assistant, VP Stores\n",
       "6        1325   51  Exec Assistant, Legal Counsel\n",
       "8        1329   48                  Store Manager"
      ],
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>EmployeeID</th>\n      <th>age</th>\n      <th>job_title</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>1</th>\n      <td>1319</td>\n      <td>58</td>\n      <td>VP Stores</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>1321</td>\n      <td>56</td>\n      <td>VP Human Resources</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>1323</td>\n      <td>53</td>\n      <td>Exec Assistant, VP Stores</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>1325</td>\n      <td>51</td>\n      <td>Exec Assistant, Legal Counsel</td>\n    </tr>\n    <tr>\n      <th>8</th>\n      <td>1329</td>\n      <td>48</td>\n      <td>Store Manager</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 21
    }
   ],
   "source": [
    "df.iloc[(df.EmployeeID%2==1).values,[0,2,-2]].head()"
   ]
  },
  {
   "source": [
    "#### 3."
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "metadata": {},
     "execution_count": 22
    }
   ],
   "source": [
    "df_op = df.copy()\n",
    "df_op = df_op.set_index(df_op.columns[-3:].tolist()).swaplevel(0,2,axis=0)\n",
    "df_op = df_op.reset_index(level=1)\n",
    "df_op = df_op.rename_axis(index={'gender':'Gender'})\n",
    "df_op.index = df_op.index.map(lambda x:'_'.join(x))\n",
    "df_op.index = df_op.index.map(lambda x:tuple(x.split('_')))\n",
    "df_op = df_op.rename_axis(index=['gender', 'department'])\n",
    "df_op = df_op.reset_index().reindex(df.columns, axis=1)\n",
    "df_op.equals(df)"
   ]
  },
  {
   "source": [
    "### Ex2：巧克力数据集\n",
    "#### 1."
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "    Company  Review Date Cocoa Percent Company Location  Rating\n",
       "0  A. Morin         2016           63%           France    3.75\n",
       "1  A. Morin         2015           70%           France    2.75\n",
       "2  A. Morin         2015           70%           France    3.00"
      ],
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>Company</th>\n      <th>Review Date</th>\n      <th>Cocoa Percent</th>\n      <th>Company Location</th>\n      <th>Rating</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>A. Morin</td>\n      <td>2016</td>\n      <td>63%</td>\n      <td>France</td>\n      <td>3.75</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>A. Morin</td>\n      <td>2015</td>\n      <td>70%</td>\n      <td>France</td>\n      <td>2.75</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>A. Morin</td>\n      <td>2015</td>\n      <td>70%</td>\n      <td>France</td>\n      <td>3.00</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 23
    }
   ],
   "source": [
    "df = pd.read_csv('../data/chocolate.csv')\n",
    "df.columns = [' '.join(i.split('\\n')) for i in df.columns]\n",
    "df.head(3)"
   ]
  },
  {
   "source": [
    "#### 2."
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "               Company  Review Date  Cocoa Percent Company Location  Rating\n",
       "33  Akesson's (Pralus)         2010           0.75      Switzerland    2.75\n",
       "34  Akesson's (Pralus)         2010           0.75      Switzerland    2.75\n",
       "36       Alain Ducasse         2014           0.75           France    2.75"
      ],
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>Company</th>\n      <th>Review Date</th>\n      <th>Cocoa Percent</th>\n      <th>Company Location</th>\n      <th>Rating</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>33</th>\n      <td>Akesson's (Pralus)</td>\n      <td>2010</td>\n      <td>0.75</td>\n      <td>Switzerland</td>\n      <td>2.75</td>\n    </tr>\n    <tr>\n      <th>34</th>\n      <td>Akesson's (Pralus)</td>\n      <td>2010</td>\n      <td>0.75</td>\n      <td>Switzerland</td>\n      <td>2.75</td>\n    </tr>\n    <tr>\n      <th>36</th>\n      <td>Alain Ducasse</td>\n      <td>2014</td>\n      <td>0.75</td>\n      <td>France</td>\n      <td>2.75</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 24
    }
   ],
   "source": [
    "df['Cocoa Percent'] = df['Cocoa Percent'].apply(lambda x:float(x[:-1])/100)\n",
    "df.query('(Rating<3)&(`Cocoa Percent`>`Cocoa Percent`.median())').head(3)"
   ]
  },
  {
   "source": [
    "#### 3."
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "                                  Company  Cocoa Percent  Rating\n",
       "Review Date Company Location                                    \n",
       "2012        Australia         Bahen & Co.            0.7     3.0\n",
       "            Australia         Bahen & Co.            0.7     2.5\n",
       "            Australia         Bahen & Co.            0.7     2.5"
      ],
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th></th>\n      <th>Company</th>\n      <th>Cocoa Percent</th>\n      <th>Rating</th>\n    </tr>\n    <tr>\n      <th>Review Date</th>\n      <th>Company Location</th>\n      <th></th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th rowspan=\"3\" valign=\"top\">2012</th>\n      <th>Australia</th>\n      <td>Bahen &amp; Co.</td>\n      <td>0.7</td>\n      <td>3.0</td>\n    </tr>\n    <tr>\n      <th>Australia</th>\n      <td>Bahen &amp; Co.</td>\n      <td>0.7</td>\n      <td>2.5</td>\n    </tr>\n    <tr>\n      <th>Australia</th>\n      <td>Bahen &amp; Co.</td>\n      <td>0.7</td>\n      <td>2.5</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 25
    }
   ],
   "source": [
    "idx = pd.IndexSlice\n",
    "exclude = ['France', 'Canada', 'Amsterdam', 'Belgium']\n",
    "res = df.set_index(['Review Date', 'Company Location']).sort_index(level=0)\n",
    "res.loc[idx[2012:,~res.index.get_level_values(1).isin(exclude)],:].head(3)"
   ]
  },
  {
   "source": [
    "## 第四章 分组\n",
    "### Ex1：汽车数据集\n",
    "#### 1."
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "                CoV          mean  count\n",
       "Country                                 \n",
       "Japan      0.387429  13938.052632     19\n",
       "Japan/USA  0.240040  10067.571429      7\n",
       "Korea      0.243435   7857.333333      3\n",
       "USA        0.203344  12543.269231     26"
      ],
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>CoV</th>\n      <th>mean</th>\n      <th>count</th>\n    </tr>\n    <tr>\n      <th>Country</th>\n      <th></th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>Japan</th>\n      <td>0.387429</td>\n      <td>13938.052632</td>\n      <td>19</td>\n    </tr>\n    <tr>\n      <th>Japan/USA</th>\n      <td>0.240040</td>\n      <td>10067.571429</td>\n      <td>7</td>\n    </tr>\n    <tr>\n      <th>Korea</th>\n      <td>0.243435</td>\n      <td>7857.333333</td>\n      <td>3</td>\n    </tr>\n    <tr>\n      <th>USA</th>\n      <td>0.203344</td>\n      <td>12543.269231</td>\n      <td>26</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 26
    }
   ],
   "source": [
    "df = pd.read_csv('../data/car.csv')\n",
    "df.groupby('Country').filter(lambda x:x.shape[0]>2).groupby('Country')['Price'].agg([('CoV', lambda x: x.std()/x.mean()), 'mean', 'count'])"
   ]
  },
  {
   "source": [
    "#### 2."
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "60"
      ]
     },
     "metadata": {},
     "execution_count": 27
    }
   ],
   "source": [
    "df.shape[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "Head     9069.95\n",
       "Mid     13356.40\n",
       "Tail    15420.65\n",
       "Name: Price, dtype: float64"
      ]
     },
     "metadata": {},
     "execution_count": 28
    }
   ],
   "source": [
    "condition = ['Head']*20+['Mid']*20+['Tail']*20\n",
    "df.groupby(condition)['Price'].mean()"
   ]
  },
  {
   "source": [
    "#### 3."
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "         Price_max  HP_min\n",
       "Type                      \n",
       "Compact      18900      95\n",
       "Large        17257     150\n",
       "Medium       24760     110\n",
       "Small         9995      63\n",
       "Sporty       13945      92\n",
       "Van          15395     106"
      ],
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>Price_max</th>\n      <th>HP_min</th>\n    </tr>\n    <tr>\n      <th>Type</th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>Compact</th>\n      <td>18900</td>\n      <td>95</td>\n    </tr>\n    <tr>\n      <th>Large</th>\n      <td>17257</td>\n      <td>150</td>\n    </tr>\n    <tr>\n      <th>Medium</th>\n      <td>24760</td>\n      <td>110</td>\n    </tr>\n    <tr>\n      <th>Small</th>\n      <td>9995</td>\n      <td>63</td>\n    </tr>\n    <tr>\n      <th>Sporty</th>\n      <td>13945</td>\n      <td>92</td>\n    </tr>\n    <tr>\n      <th>Van</th>\n      <td>15395</td>\n      <td>106</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 29
    }
   ],
   "source": [
    "res = df.groupby('Type').agg({'Price': ['max'], 'HP': ['min']})\n",
    "res.columns = res.columns.map(lambda x:'_'.join(x))\n",
    "res"
   ]
  },
  {
   "source": [
    "#### 4."
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "0    1.00\n",
       "1    0.54\n",
       "2    0.00\n",
       "3    0.58\n",
       "4    0.80\n",
       "Name: HP, dtype: float64"
      ]
     },
     "metadata": {},
     "execution_count": 30
    }
   ],
   "source": [
    "def normalize(s):\n",
    "    s_min, s_max = s.min(), s.max()\n",
    "    res = (s - s_min)/(s_max - s_min)\n",
    "    return res\n",
    "df.groupby('Type')['HP'].transform(normalize).head()"
   ]
  },
  {
   "source": [
    "#### 5."
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "Type\n",
       "Compact    0.586087\n",
       "Large     -0.242765\n",
       "Medium     0.370491\n",
       "Small      0.603916\n",
       "Sporty     0.871426\n",
       "Van        0.819881\n",
       "dtype: float64"
      ]
     },
     "metadata": {},
     "execution_count": 31
    }
   ],
   "source": [
    "df.groupby('Type')[['HP', 'Disp.']].apply(lambda x:np.corrcoef(x['HP'].values, x['Disp.'].values)[0,1])"
   ]
  },
  {
   "source": [
    "### Ex2：实现transform函数"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "<__main__.my_groupby at 0x2b38e674648>"
      ]
     },
     "metadata": {},
     "execution_count": 32
    }
   ],
   "source": [
    "class my_groupby:\n",
    "    def __init__(self, my_df, group_cols):\n",
    "        self.my_df = my_df.copy()\n",
    "        self.groups = my_df[group_cols].drop_duplicates()\n",
    "        if isinstance(self.groups, pd.Series):\n",
    "            self.groups = self.groups.to_frame()\n",
    "        self.group_cols = self.groups.columns.tolist()\n",
    "        self.groups = {i: self.groups[i].values.tolist() for i in self.groups.columns}\n",
    "        self.transform_col = None\n",
    "    def __getitem__(self, col):\n",
    "        self.pr_col = [col] if isinstance(col, str) else list(col)\n",
    "        return self\n",
    "    def transform(self, my_func):\n",
    "        self.num = len(self.groups[self.group_cols[0]])\n",
    "        L_order, L_value = np.array([]), np.array([])\n",
    "        for i in range(self.num):\n",
    "            group_df = self.my_df.reset_index().copy()\n",
    "            for col in self.group_cols:\n",
    "                group_df = group_df[group_df[col]==self.groups[col][i]]\n",
    "            group_df = group_df[self.pr_col]\n",
    "            if group_df.shape[1] == 1:\n",
    "                group_df = group_df.iloc[:, 0]\n",
    "            group_res = my_func(group_df)\n",
    "            if not isinstance(group_res, pd.Series):\n",
    "                group_res = pd.Series(group_res,index=group_df.index,name=group_df.name)\n",
    "            L_order = np.r_[L_order, group_res.index]\n",
    "            L_value = np.r_[L_value, group_res.values]\n",
    "        self.res = pd.Series(pd.Series(L_value, index=L_order).sort_index().values,index=self.my_df.reset_index().index, name=my_func.__name__)\n",
    "        return self.res\n",
    "\n",
    "my_groupby(df, 'Type')"
   ]
  },
  {
   "source": [
    "单列分组："
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "0    0.733592\n",
       "1    0.372003\n",
       "2    0.109712\n",
       "3    0.186244\n",
       "4    0.177525\n",
       "Name: f, dtype: float64"
      ]
     },
     "metadata": {},
     "execution_count": 33
    }
   ],
   "source": [
    "def f(s):\n",
    "    res = (s-s.min())/(s.max()-s.min())\n",
    "    return res\n",
    "my_groupby(df, 'Type')['Price'].transform(f).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "0    0.733592\n",
       "1    0.372003\n",
       "2    0.109712\n",
       "3    0.186244\n",
       "4    0.177525\n",
       "Name: Price, dtype: float64"
      ]
     },
     "metadata": {},
     "execution_count": 34
    }
   ],
   "source": [
    "df.groupby('Type')['Price'].transform(f).head()"
   ]
  },
  {
   "source": [
    "多列分组："
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "0    1.000000\n",
       "1    0.000000\n",
       "2    0.000000\n",
       "3    0.000000\n",
       "4    0.196357\n",
       "Name: f, dtype: float64"
      ]
     },
     "metadata": {},
     "execution_count": 35
    }
   ],
   "source": [
    "my_groupby(df, ['Type','Country'])['Price'].transform(f).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "0    1.000000\n",
       "1    0.000000\n",
       "2    0.000000\n",
       "3    0.000000\n",
       "4    0.196357\n",
       "Name: Price, dtype: float64"
      ]
     },
     "metadata": {},
     "execution_count": 36
    }
   ],
   "source": [
    "df.groupby(['Type','Country'])['Price'].transform(f).head()"
   ]
  },
  {
   "source": [
    "标量广播："
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "0    7682.384615\n",
       "1    7682.384615\n",
       "2    7682.384615\n",
       "3    7682.384615\n",
       "4    7682.384615\n",
       "Name: <lambda>, dtype: float64"
      ]
     },
     "metadata": {},
     "execution_count": 37
    }
   ],
   "source": [
    "my_groupby(df, 'Type')['Price'].transform(lambda x:x.mean()).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "0    7682.384615\n",
       "1    7682.384615\n",
       "2    7682.384615\n",
       "3    7682.384615\n",
       "4    7682.384615\n",
       "Name: Price, dtype: float64"
      ]
     },
     "metadata": {},
     "execution_count": 38
    }
   ],
   "source": [
    "df.groupby('Type')['Price'].transform(lambda x:x.mean()).head()"
   ]
  },
  {
   "source": [
    "跨列计算："
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "0    0.858407\n",
       "1    1.266667\n",
       "2    1.285714\n",
       "3    0.989130\n",
       "4    1.097087\n",
       "Name: <lambda>, dtype: float64"
      ]
     },
     "metadata": {},
     "execution_count": 39
    }
   ],
   "source": [
    "my_groupby(df, 'Type')['Disp.', 'HP'].transform(lambda x: x['Disp.']/x.HP).head()"
   ]
  },
  {
   "source": [
    "## 第五章 变形\n",
    "### Ex1：美国非法药物数据集\n",
    "#### 1."
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "source": [
    "df = pd.read_csv('../data/drugs.csv').sort_values(['State','COUNTY','SubstanceName'],ignore_index=True)\n",
    "res = df.pivot(index=['State','COUNTY','SubstanceName'], columns='YYYY', values='DrugReports').reset_index().rename_axis(columns={'YYYY':''})\n",
    "res.head(5)"
   ],
   "cell_type": "code",
   "metadata": {},
   "execution_count": 40,
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "  State COUNTY  SubstanceName  2010  2011  2012  2013  2014  2015  2016  2017\n",
       "0    KY  ADAIR  Buprenorphine   NaN   3.0   5.0   4.0  27.0   5.0   7.0  10.0\n",
       "1    KY  ADAIR        Codeine   NaN   NaN   1.0   NaN   NaN   NaN   NaN   1.0\n",
       "2    KY  ADAIR       Fentanyl   NaN   NaN   1.0   NaN   NaN   NaN   NaN   NaN\n",
       "3    KY  ADAIR         Heroin   NaN   NaN   1.0   2.0   NaN   1.0   NaN   2.0\n",
       "4    KY  ADAIR    Hydrocodone   6.0   9.0  10.0  10.0   9.0   7.0  11.0   3.0"
      ],
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>State</th>\n      <th>COUNTY</th>\n      <th>SubstanceName</th>\n      <th>2010</th>\n      <th>2011</th>\n      <th>2012</th>\n      <th>2013</th>\n      <th>2014</th>\n      <th>2015</th>\n      <th>2016</th>\n      <th>2017</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>KY</td>\n      <td>ADAIR</td>\n      <td>Buprenorphine</td>\n      <td>NaN</td>\n      <td>3.0</td>\n      <td>5.0</td>\n      <td>4.0</td>\n      <td>27.0</td>\n      <td>5.0</td>\n      <td>7.0</td>\n      <td>10.0</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>KY</td>\n      <td>ADAIR</td>\n      <td>Codeine</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>1.0</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>1.0</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>KY</td>\n      <td>ADAIR</td>\n      <td>Fentanyl</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>1.0</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>KY</td>\n      <td>ADAIR</td>\n      <td>Heroin</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>1.0</td>\n      <td>2.0</td>\n      <td>NaN</td>\n      <td>1.0</td>\n      <td>NaN</td>\n      <td>2.0</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>KY</td>\n      <td>ADAIR</td>\n      <td>Hydrocodone</td>\n      <td>6.0</td>\n      <td>9.0</td>\n      <td>10.0</td>\n      <td>10.0</td>\n      <td>9.0</td>\n      <td>7.0</td>\n      <td>11.0</td>\n      <td>3.0</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 40
    }
   ]
  },
  {
   "source": [
    "#### 2."
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "metadata": {},
     "execution_count": 41
    }
   ],
   "source": [
    "res_melted = res.melt(id_vars = ['State','COUNTY','SubstanceName'],\n",
    "                      value_vars = res.columns[-8:],\n",
    "                      var_name = 'YYYY',\n",
    "                      value_name = 'DrugReports').dropna(\n",
    "                      subset=['DrugReports'])\n",
    "res_melted = res_melted[df.columns].sort_values(['State','COUNTY','SubstanceName'],ignore_index=True).astype({'YYYY':'int64', 'DrugReports':'int64'})\n",
    "res_melted.equals(df)"
   ]
  },
  {
   "source": [
    "#### 3.\n",
    "策略一："
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "State     KY     OH     PA    VA    WV\n",
       "YYYY                                  \n",
       "2010   10453  19707  19814  8685  2890\n",
       "2011   10289  20330  19987  6749  3271\n",
       "2012   10722  23145  19959  7831  3376"
      ],
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th>State</th>\n      <th>KY</th>\n      <th>OH</th>\n      <th>PA</th>\n      <th>VA</th>\n      <th>WV</th>\n    </tr>\n    <tr>\n      <th>YYYY</th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>2010</th>\n      <td>10453</td>\n      <td>19707</td>\n      <td>19814</td>\n      <td>8685</td>\n      <td>2890</td>\n    </tr>\n    <tr>\n      <th>2011</th>\n      <td>10289</td>\n      <td>20330</td>\n      <td>19987</td>\n      <td>6749</td>\n      <td>3271</td>\n    </tr>\n    <tr>\n      <th>2012</th>\n      <td>10722</td>\n      <td>23145</td>\n      <td>19959</td>\n      <td>7831</td>\n      <td>3376</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 42
    }
   ],
   "source": [
    "res = df.pivot_table(index='YYYY', columns='State', values='DrugReports', aggfunc='sum')\n",
    "res.head(3)"
   ]
  },
  {
   "source": [
    "策略二："
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "State     KY     OH     PA    VA    WV\n",
       "YYYY                                  \n",
       "2010   10453  19707  19814  8685  2890\n",
       "2011   10289  20330  19987  6749  3271\n",
       "2012   10722  23145  19959  7831  3376"
      ],
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th>State</th>\n      <th>KY</th>\n      <th>OH</th>\n      <th>PA</th>\n      <th>VA</th>\n      <th>WV</th>\n    </tr>\n    <tr>\n      <th>YYYY</th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>2010</th>\n      <td>10453</td>\n      <td>19707</td>\n      <td>19814</td>\n      <td>8685</td>\n      <td>2890</td>\n    </tr>\n    <tr>\n      <th>2011</th>\n      <td>10289</td>\n      <td>20330</td>\n      <td>19987</td>\n      <td>6749</td>\n      <td>3271</td>\n    </tr>\n    <tr>\n      <th>2012</th>\n      <td>10722</td>\n      <td>23145</td>\n      <td>19959</td>\n      <td>7831</td>\n      <td>3376</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 43
    }
   ],
   "source": [
    "res = df.groupby(['State', 'YYYY'])['DrugReports'].sum().to_frame().unstack(0).droplevel(0,axis=1)\n",
    "res.head(3)"
   ]
  },
  {
   "source": [
    "### Ex2：特殊的wide_to_long方法"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "   Class       Name  Chinese  Math\n",
       "0      1  San Zhang       80    80\n",
       "1      2      Si Li       90    75"
      ],
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>Class</th>\n      <th>Name</th>\n      <th>Chinese</th>\n      <th>Math</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>1</td>\n      <td>San Zhang</td>\n      <td>80</td>\n      <td>80</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>2</td>\n      <td>Si Li</td>\n      <td>90</td>\n      <td>75</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 44
    }
   ],
   "source": [
    "df = pd.DataFrame({'Class':[1,2],\n",
    "                   'Name':['San Zhang', 'Si Li'],\n",
    "                   'Chinese':[80, 90],\n",
    "                   'Math':[80, 75]})\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "   Class       Name  Subject  Grade\n",
       "0      1  San Zhang  Chinese     80\n",
       "1      1  San Zhang     Math     80\n",
       "2      2      Si Li  Chinese     90\n",
       "3      2      Si Li     Math     75"
      ],
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>Class</th>\n      <th>Name</th>\n      <th>Subject</th>\n      <th>Grade</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>1</td>\n      <td>San Zhang</td>\n      <td>Chinese</td>\n      <td>80</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>1</td>\n      <td>San Zhang</td>\n      <td>Math</td>\n      <td>80</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>2</td>\n      <td>Si Li</td>\n      <td>Chinese</td>\n      <td>90</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>2</td>\n      <td>Si Li</td>\n      <td>Math</td>\n      <td>75</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 45
    }
   ],
   "source": [
    "df = df.rename(columns={'Chinese':'pre_Chinese', 'Math':'pre_Math'})\n",
    "pd.wide_to_long(df,\n",
    "                stubnames=['pre'],\n",
    "                i = ['Class', 'Name'],\n",
    "                j='Subject',\n",
    "                sep='_',\n",
    "                suffix='.+').reset_index().rename(columns={'pre':'Grade'})"
   ]
  },
  {
   "source": [
    "## 第六章 连接\n",
    "### Ex1：美国疫情数据集"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "date = pd.date_range('20200412', '20201116').to_series()\n",
    "date = date.dt.month.astype('string').str.zfill(2) +'-'+ date.dt.day.astype('string').str.zfill(2) +'-'+ '2020'\n",
    "date = date.tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "           Confirmed Deaths Recovered    Active\n",
       "04-12-2020    189033   9385   23887.0    179648\n",
       "04-13-2020    195749  10058   23887.0  185691.0\n",
       "04-14-2020    203020  10842   23887.0  192178.0\n",
       "04-15-2020    214454  11617   23887.0  202837.0\n",
       "04-16-2020    223691  14832   23887.0  208859.0"
      ],
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>Confirmed</th>\n      <th>Deaths</th>\n      <th>Recovered</th>\n      <th>Active</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>04-12-2020</th>\n      <td>189033</td>\n      <td>9385</td>\n      <td>23887.0</td>\n      <td>179648</td>\n    </tr>\n    <tr>\n      <th>04-13-2020</th>\n      <td>195749</td>\n      <td>10058</td>\n      <td>23887.0</td>\n      <td>185691.0</td>\n    </tr>\n    <tr>\n      <th>04-14-2020</th>\n      <td>203020</td>\n      <td>10842</td>\n      <td>23887.0</td>\n      <td>192178.0</td>\n    </tr>\n    <tr>\n      <th>04-15-2020</th>\n      <td>214454</td>\n      <td>11617</td>\n      <td>23887.0</td>\n      <td>202837.0</td>\n    </tr>\n    <tr>\n      <th>04-16-2020</th>\n      <td>223691</td>\n      <td>14832</td>\n      <td>23887.0</td>\n      <td>208859.0</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 47
    }
   ],
   "source": [
    "L = []\n",
    "for d in date:\n",
    "    df = pd.read_csv('../data/us_report/' + d + '.csv', index_col='Province_State')\n",
    "    data = df.loc['New York', ['Confirmed','Deaths','Recovered','Active']]\n",
    "    L.append(data.to_frame().T)\n",
    "res = pd.concat(L)\n",
    "res.index = date\n",
    "res.head()"
   ]
  },
  {
   "source": [
    "### Ex2：实现join函数"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "def join(df1, df2, how='left'):\n",
    "    res_col = df1.columns.tolist() +  df2.columns.tolist()\n",
    "    dup = df1.index.unique().intersection(df2.index.unique())\n",
    "    res_df = pd.DataFrame(columns = res_col)\n",
    "    for label in dup:\n",
    "        cartesian = [list(i)+list(j) for i in df1.loc[label].values.reshape(-1,1) for j in df2.loc[label].values.reshape(-1,1)]\n",
    "        dup_df = pd.DataFrame(cartesian, index = [label]*len(cartesian), columns = res_col)\n",
    "        res_df = pd.concat([res_df,dup_df])\n",
    "    if how in ['left', 'outer']:\n",
    "        for label in df1.index.unique().difference(dup):\n",
    "            if isinstance(df1.loc[label], pd.DataFrame):\n",
    "                cat = [list(i)+[np.nan]*df2.shape[1] for i in df1.loc[label].values]\n",
    "            else:\n",
    "                cat = [list(i)+[np.nan]*df2.shape[1] for i in df1.loc[label].to_frame().values]\n",
    "            dup_df = pd.DataFrame(cat, index = [label]*len(cat), columns = res_col)\n",
    "            res_df = pd.concat([res_df,dup_df])\n",
    "    if how in ['right', 'outer']:\n",
    "        for label in df2.index.unique().difference(dup):\n",
    "            if isinstance(df2.loc[label], pd.DataFrame):\n",
    "                cat = [[np.nan]+list(i)*df1.shape[1] for i in df2.loc[label].values]\n",
    "            else:\n",
    "                cat = [[np.nan]+list(i)*df1.shape[1] for i in df2.loc[label].to_frame().values]\n",
    "            dup_df = pd.DataFrame(cat, index = [label]*len(cat), columns = res_col)\n",
    "            res_df = pd.concat([res_df,dup_df])\n",
    "    return res_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "   col1\n",
       "A     1\n",
       "A     2\n",
       "B     3\n",
       "C     4\n",
       "D     5"
      ],
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>col1</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>A</th>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>A</th>\n      <td>2</td>\n    </tr>\n    <tr>\n      <th>B</th>\n      <td>3</td>\n    </tr>\n    <tr>\n      <th>C</th>\n      <td>4</td>\n    </tr>\n    <tr>\n      <th>D</th>\n      <td>5</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 49
    }
   ],
   "source": [
    "df1 = pd.DataFrame({'col1':[1,2,3,4,5]}, index=list('AABCD'))\n",
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "  col2\n",
       "A    o\n",
       "B    p\n",
       "B    q\n",
       "C    r\n",
       "E    s\n",
       "E    t"
      ],
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>col2</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>A</th>\n      <td>o</td>\n    </tr>\n    <tr>\n      <th>B</th>\n      <td>p</td>\n    </tr>\n    <tr>\n      <th>B</th>\n      <td>q</td>\n    </tr>\n    <tr>\n      <th>C</th>\n      <td>r</td>\n    </tr>\n    <tr>\n      <th>E</th>\n      <td>s</td>\n    </tr>\n    <tr>\n      <th>E</th>\n      <td>t</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 50
    }
   ],
   "source": [
    "df2 = pd.DataFrame({'col2':list('opqrst')}, index=list('ABBCEE'))\n",
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "  col1 col2\n",
       "A    1    o\n",
       "A    2    o\n",
       "B    3    p\n",
       "B    3    q\n",
       "C    4    r"
      ],
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>col1</th>\n      <th>col2</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>A</th>\n      <td>1</td>\n      <td>o</td>\n    </tr>\n    <tr>\n      <th>A</th>\n      <td>2</td>\n      <td>o</td>\n    </tr>\n    <tr>\n      <th>B</th>\n      <td>3</td>\n      <td>p</td>\n    </tr>\n    <tr>\n      <th>B</th>\n      <td>3</td>\n      <td>q</td>\n    </tr>\n    <tr>\n      <th>C</th>\n      <td>4</td>\n      <td>r</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 51
    }
   ],
   "source": [
    "join(df1, df2, how='inner')"
   ]
  },
  {
   "source": [
    "## 第七章 缺失数据\n",
    "### Ex1：缺失值与类别的相关性检验"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "0.9712760884395901"
      ]
     },
     "metadata": {},
     "execution_count": 52
    }
   ],
   "source": [
    "df = pd.read_csv('../data/missing_chi.csv')\n",
    "cat_1 = df.X_1.fillna('NaN').mask(df.X_1.notna()).fillna(\"NotNaN\")\n",
    "cat_2 = df.X_2.fillna('NaN').mask(df.X_2.notna()).fillna(\"NotNaN\")\n",
    "df_1 = pd.crosstab(cat_1, df.y, margins=True)\n",
    "df_2 = pd.crosstab(cat_2, df.y, margins=True)\n",
    "def compute_S(my_df):\n",
    "    S = []\n",
    "    for i in range(2):\n",
    "        for j in range(2):\n",
    "            E = my_df.iat[i, j]\n",
    "            F = my_df.iat[i, 2]*my_df.iat[2, j]/my_df.iat[2,2]\n",
    "            S.append((E-F)**2/F)\n",
    "    return sum(S)\n",
    "res1 = compute_S(df_1)\n",
    "res2 = compute_S(df_2)\n",
    "from scipy.stats import chi2\n",
    "chi2.sf(res1, 1) # X_1检验的p值 # 不能认为相关，剔除"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "7.459641265637543e-166"
      ]
     },
     "metadata": {},
     "execution_count": 53
    }
   ],
   "source": [
    "chi2.sf(res2, 1) # X_2检验的p值 # 认为相关，保留"
   ]
  },
  {
   "source": [
    "结果与`scipy.stats.chi2_contingency`在不使用$Yates$修正的情况下完全一致："
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "7.459641265637543e-166"
      ]
     },
     "metadata": {},
     "execution_count": 54
    }
   ],
   "source": [
    "from scipy.stats import chi2_contingency\n",
    "chi2_contingency(pd.crosstab(cat_1, df.y), correction=False)[1]\n",
    "chi2_contingency(pd.crosstab(cat_2, df.y), correction=False)[1]"
   ]
  },
  {
   "source": [
    "### Ex2：用回归模型解决分类问题\n",
    "#### 1."
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "'Yellow'"
      ]
     },
     "metadata": {},
     "execution_count": 55
    }
   ],
   "source": [
    "from sklearn.neighbors import KNeighborsRegressor\n",
    "df = pd.read_excel('../data/color.xlsx')\n",
    "df_dummies = pd.get_dummies(df.Color)\n",
    "stack_list = []\n",
    "for col in df_dummies.columns:\n",
    "    clf = KNeighborsRegressor(n_neighbors=6)\n",
    "    clf.fit(df.iloc[:,:2], df_dummies[col])\n",
    "    res = clf.predict([[0.8, -0.2]]).reshape(-1,1)\n",
    "    stack_list.append(res)\n",
    "code_res = pd.Series(np.hstack(stack_list).argmax(1))\n",
    "df_dummies.columns[code_res[0]]"
   ]
  },
  {
   "source": [
    "#### 2."
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "ID            0\n",
       "Age           0\n",
       "Employment    0\n",
       "Marital       0\n",
       "Income        0\n",
       "Gender        0\n",
       "Hours         0\n",
       "dtype: int64"
      ]
     },
     "metadata": {},
     "execution_count": 56
    }
   ],
   "source": [
    "from sklearn.neighbors import KNeighborsRegressor\n",
    "df = pd.read_csv('../data/audit.csv')\n",
    "res_df = df.copy()\n",
    "df = pd.concat([pd.get_dummies(df[['Marital', 'Gender']]), df[['Age','Income','Hours']].apply(lambda x:(x-x.min())/(x.max()-x.min())), df.Employment],1)\n",
    "X_train = df.query('Employment.notna()')\n",
    "X_test = df.query('Employment.isna()')\n",
    "df_dummies = pd.get_dummies(X_train.Employment)\n",
    "stack_list = []\n",
    "for col in df_dummies.columns:\n",
    "    clf = KNeighborsRegressor(n_neighbors=6)\n",
    "    clf.fit(X_train.iloc[:,:-1], df_dummies[col])\n",
    "    res = clf.predict(X_test.iloc[:,:-1]).reshape(-1,1)\n",
    "    stack_list.append(res)\n",
    "code_res = pd.Series(np.hstack(stack_list).argmax(1))\n",
    "cat_res = code_res.replace(dict(zip(list(range(df_dummies.shape[0])),df_dummies.columns)))\n",
    "res_df.loc[res_df.Employment.isna(), 'Employment'] = cat_res.values\n",
    "res_df.isna().sum()"
   ]
  },
  {
   "source": [
    "## 第八章 文本数据\n",
    "### Ex1：房屋信息数据集\n",
    "#### 1."
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "      floor  year    area price\n",
       "0   高层（共6层）  1986  58.23㎡  155万\n",
       "1  中层（共20层）  2020     88㎡  155万\n",
       "2  低层（共28层）  2010  89.33㎡  365万"
      ],
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>floor</th>\n      <th>year</th>\n      <th>area</th>\n      <th>price</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>高层（共6层）</td>\n      <td>1986</td>\n      <td>58.23㎡</td>\n      <td>155万</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>中层（共20层）</td>\n      <td>2020</td>\n      <td>88㎡</td>\n      <td>155万</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>低层（共28层）</td>\n      <td>2010</td>\n      <td>89.33㎡</td>\n      <td>365万</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 57
    }
   ],
   "source": [
    "df = pd.read_excel('../data/house_info.xls', usecols=['floor','year','area','price'])\n",
    "df.year = pd.to_numeric(df.year.str[:-2]).astype('Int64')\n",
    "df.head(3)"
   ]
  },
  {
   "source": [
    "#### 2."
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "source": [
    "pat = '(\\w层)（共(\\d+)层）'\n",
    "new_cols = df.floor.str.extract(pat).rename(columns={0:'Level', 1:'Highest'})\n",
    "df = pd.concat([df.drop(columns=['floor']), new_cols], 1)\n",
    "df.head(3)"
   ],
   "cell_type": "code",
   "metadata": {},
   "execution_count": 58,
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "   year    area price Level Highest\n",
       "0  1986  58.23㎡  155万    高层       6\n",
       "1  2020     88㎡  155万    中层      20\n",
       "2  2010  89.33㎡  365万    低层      28"
      ],
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>year</th>\n      <th>area</th>\n      <th>price</th>\n      <th>Level</th>\n      <th>Highest</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>1986</td>\n      <td>58.23㎡</td>\n      <td>155万</td>\n      <td>高层</td>\n      <td>6</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>2020</td>\n      <td>88㎡</td>\n      <td>155万</td>\n      <td>中层</td>\n      <td>20</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>2010</td>\n      <td>89.33㎡</td>\n      <td>365万</td>\n      <td>低层</td>\n      <td>28</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 58
    }
   ]
  },
  {
   "source": [
    "#### 3."
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "   year    area price Level Highest  avg_price\n",
       "0  1986  58.23㎡  155万    高层       6  26618元/平米\n",
       "1  2020     88㎡  155万    中层      20  17613元/平米\n",
       "2  2010  89.33㎡  365万    低层      28  40859元/平米"
      ],
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>year</th>\n      <th>area</th>\n      <th>price</th>\n      <th>Level</th>\n      <th>Highest</th>\n      <th>avg_price</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>1986</td>\n      <td>58.23㎡</td>\n      <td>155万</td>\n      <td>高层</td>\n      <td>6</td>\n      <td>26618元/平米</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>2020</td>\n      <td>88㎡</td>\n      <td>155万</td>\n      <td>中层</td>\n      <td>20</td>\n      <td>17613元/平米</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>2010</td>\n      <td>89.33㎡</td>\n      <td>365万</td>\n      <td>低层</td>\n      <td>28</td>\n      <td>40859元/平米</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 59
    }
   ],
   "source": [
    "s_area = pd.to_numeric(df.area.str[:-1])\n",
    "s_price = pd.to_numeric(df.price.str[:-1])\n",
    "df['avg_price'] = ((s_price/s_area)*10000).astype('int').astype('string') + '元/平米'\n",
    "df.head(3)"
   ]
  },
  {
   "source": [
    "### Ex2：《权力的游戏》剧本数据集\n",
    "#### 1."
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "Season    Episode   \n",
       "Season 1  Episode 1     327\n",
       "          Episode 10    266\n",
       "          Episode 2     283\n",
       "          Episode 3     353\n",
       "          Episode 4     404\n",
       "Name: Sentence, dtype: int64"
      ]
     },
     "metadata": {},
     "execution_count": 60
    }
   ],
   "source": [
    "df = pd.read_csv('../data/script.csv')\n",
    "df.columns = df.columns.str.strip()\n",
    "df.groupby(['Season', 'Episode'])['Sentence'].count().head()"
   ]
  },
  {
   "source": [
    "#### 2."
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "Name\n",
       "male singer          109.000000\n",
       "slave owner           77.000000\n",
       "manderly              62.000000\n",
       "lollys stokeworth     62.000000\n",
       "dothraki matron       56.666667\n",
       "Name: Sentence, dtype: float64"
      ]
     },
     "metadata": {},
     "execution_count": 61
    }
   ],
   "source": [
    "df.set_index('Name').Sentence.str.split().str.len().groupby('Name').mean().sort_values(ascending=False).head()"
   ]
  },
  {
   "source": [
    "#### 3."
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "Name\n",
       "tyrion lannister    527\n",
       "jon snow            374\n",
       "jaime lannister     283\n",
       "arya stark          265\n",
       "cersei lannister    246\n",
       "dtype: int64"
      ]
     },
     "metadata": {},
     "execution_count": 62
    }
   ],
   "source": [
    "s = pd.Series(df.Sentence.values, index=df.Name.shift(-1))\n",
    "s.str.count('\\?').groupby('Name').sum().sort_values(ascending=False).head()"
   ]
  },
  {
   "source": [
    "## 第九章 分类数据\n",
    "### Ex1：统计未出现的类别"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "B  cat  dog  sheep\n",
       "A                 \n",
       "a    2    0      0\n",
       "b    1    0      0\n",
       "c    0    1      0"
      ],
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th>B</th>\n      <th>cat</th>\n      <th>dog</th>\n      <th>sheep</th>\n    </tr>\n    <tr>\n      <th>A</th>\n      <th></th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>a</th>\n      <td>2</td>\n      <td>0</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>b</th>\n      <td>1</td>\n      <td>0</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>c</th>\n      <td>0</td>\n      <td>1</td>\n      <td>0</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 63
    }
   ],
   "source": [
    "def my_crosstab(s1, s2, dropna=True):\n",
    "    idx1 = (s1.cat.categories if s1.dtype.name == 'category' and not dropna else s1.unique())\n",
    "    idx2 = (s2.cat.categories if s2.dtype.name == 'category' and not dropna else s2.unique())\n",
    "    res = pd.DataFrame(np.zeros((idx1.shape[0], idx2.shape[0])), index=idx1, columns=idx2)\n",
    "    for i, j in zip(s1, s2):\n",
    "        res.at[i, j] += 1\n",
    "    res = res.rename_axis(index=s1.name, columns=s2.name).astype('int')\n",
    "    return res\n",
    "df = pd.DataFrame({'A':['a','b','c','a'], 'B':['cat','cat','dog','cat']})\n",
    "df.B = df.B.astype('category').cat.add_categories('sheep')\n",
    "my_crosstab(df.A, df.B)\n",
    "my_crosstab(df.A, df.B, dropna=False)"
   ]
  },
  {
   "source": [
    "### Ex2：钻石数据集\n",
    "#### 1."
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('../data/diamonds.csv')\n",
    "s_obj, s_cat = df.cut, df.cut.astype('category')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "5.52 ms ± 207 µs per loop (mean ± std. dev. of 7 runs, 30 loops each)\n"
     ]
    }
   ],
   "source": [
    "%timeit -n 30 s_obj.nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "1.15 ms ± 85.8 µs per loop (mean ± std. dev. of 7 runs, 30 loops each)\n"
     ]
    }
   ],
   "source": [
    "%timeit -n 30 s_cat.nunique()"
   ]
  },
  {
   "source": [
    "#### 2."
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "     carat    cut clarity  price\n",
       "315   0.96  Ideal      I1   2801\n",
       "535   0.96  Ideal      I1   2826\n",
       "551   0.97  Ideal      I1   2830"
      ],
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>carat</th>\n      <th>cut</th>\n      <th>clarity</th>\n      <th>price</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>315</th>\n      <td>0.96</td>\n      <td>Ideal</td>\n      <td>I1</td>\n      <td>2801</td>\n    </tr>\n    <tr>\n      <th>535</th>\n      <td>0.96</td>\n      <td>Ideal</td>\n      <td>I1</td>\n      <td>2826</td>\n    </tr>\n    <tr>\n      <th>551</th>\n      <td>0.97</td>\n      <td>Ideal</td>\n      <td>I1</td>\n      <td>2830</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 67
    }
   ],
   "source": [
    "df.cut = df.cut.astype('category').cat.reorder_categories(['Fair', 'Good', 'Very Good', 'Premium', 'Ideal'],ordered=True)\n",
    "df.clarity = df.clarity.astype('category').cat.reorder_categories(['I1', 'SI2', 'SI1', 'VS2', 'VS1', 'VVS2', 'VVS1', 'IF'],ordered=True)\n",
    "res = df.sort_values(['cut', 'clarity'], ascending=[False, True])\n",
    "res.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "       carat   cut clarity  price\n",
       "47407   0.52  Fair      IF   1849\n",
       "49683   0.52  Fair      IF   2144\n",
       "50126   0.47  Fair      IF   2211"
      ],
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>carat</th>\n      <th>cut</th>\n      <th>clarity</th>\n      <th>price</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>47407</th>\n      <td>0.52</td>\n      <td>Fair</td>\n      <td>IF</td>\n      <td>1849</td>\n    </tr>\n    <tr>\n      <th>49683</th>\n      <td>0.52</td>\n      <td>Fair</td>\n      <td>IF</td>\n      <td>2144</td>\n    </tr>\n    <tr>\n      <th>50126</th>\n      <td>0.47</td>\n      <td>Fair</td>\n      <td>IF</td>\n      <td>2211</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 68
    }
   ],
   "source": [
    "res.tail(3)"
   ]
  },
  {
   "source": [
    "#### 3."
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.cut = df.cut.cat.reorder_categories(df.cut.cat.categories[::-1])\n",
    "df.clarity = df.clarity.cat.reorder_categories(df.clarity.cat.categories[::-1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.cut = df.cut.cat.codes # 方法一：利用cat.codes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [],
   "source": [
    "clarity_cat = df.clarity.cat.categories\n",
    "df.clarity = df.clarity.replace(dict(zip(clarity_cat, np.arange(len(clarity_cat))))) # 方法二：使用replace映射"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "   carat  cut  clarity  price\n",
       "0   0.23    0        6    326\n",
       "1   0.21    1        5    326\n",
       "2   0.23    3        3    327"
      ],
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>carat</th>\n      <th>cut</th>\n      <th>clarity</th>\n      <th>price</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>0.23</td>\n      <td>0</td>\n      <td>6</td>\n      <td>326</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>0.21</td>\n      <td>1</td>\n      <td>5</td>\n      <td>326</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>0.23</td>\n      <td>3</td>\n      <td>3</td>\n      <td>327</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 72
    }
   ],
   "source": [
    "df.head(3)"
   ]
  },
  {
   "source": [
    "#### 4."
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "   carat  cut  clarity  price avg_cut  avg_qcut\n",
       "0   0.23    0        6    326     Low  Very Low\n",
       "1   0.21    1        5    326     Low  Very Low\n",
       "2   0.23    3        3    327     Low  Very Low\n",
       "3   0.29    1        4    334     Low  Very Low\n",
       "4   0.31    3        6    335     Low  Very Low"
      ],
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>carat</th>\n      <th>cut</th>\n      <th>clarity</th>\n      <th>price</th>\n      <th>avg_cut</th>\n      <th>avg_qcut</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>0.23</td>\n      <td>0</td>\n      <td>6</td>\n      <td>326</td>\n      <td>Low</td>\n      <td>Very Low</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>0.21</td>\n      <td>1</td>\n      <td>5</td>\n      <td>326</td>\n      <td>Low</td>\n      <td>Very Low</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>0.23</td>\n      <td>3</td>\n      <td>3</td>\n      <td>327</td>\n      <td>Low</td>\n      <td>Very Low</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>0.29</td>\n      <td>1</td>\n      <td>4</td>\n      <td>334</td>\n      <td>Low</td>\n      <td>Very Low</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>0.31</td>\n      <td>3</td>\n      <td>6</td>\n      <td>335</td>\n      <td>Low</td>\n      <td>Very Low</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 73
    }
   ],
   "source": [
    "q = [0, 0.2, 0.4, 0.6, 0.8, 1]\n",
    "point = [-np.infty, 1000, 3500, 5500, 18000, np.infty]\n",
    "avg = df.price / df.carat\n",
    "df['avg_cut'] = pd.cut(avg, bins=point, labels=['Very Low', 'Low', 'Mid', 'High', 'Very High'])\n",
    "df['avg_qcut'] = pd.qcut(avg, q=q, labels=['Very Low', 'Low', 'Mid', 'High', 'Very High'])\n",
    "df.head()"
   ]
  },
  {
   "source": [
    "#### 5."
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "['Low', 'Mid', 'High']\n",
       "Categories (3, object): ['Low' < 'Mid' < 'High']"
      ]
     },
     "metadata": {},
     "execution_count": 74
    }
   ],
   "source": [
    "df.avg_cut.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "Index(['Very Low', 'Low', 'Mid', 'High', 'Very High'], dtype='object')"
      ]
     },
     "metadata": {},
     "execution_count": 75
    }
   ],
   "source": [
    "df.avg_cut.cat.categories"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "0    Low\n",
       "1    Low\n",
       "2    Low\n",
       "Name: avg_cut, dtype: category\n",
       "Categories (3, object): ['Low' < 'Mid' < 'High']"
      ]
     },
     "metadata": {},
     "execution_count": 76
    }
   ],
   "source": [
    "df.avg_cut = df.avg_cut.cat.remove_categories(['Very Low', 'Very High'])\n",
    "df.avg_cut.head(3)"
   ]
  },
  {
   "source": [
    "#### 6."
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "0    2295.0\n",
       "1    2295.0\n",
       "2    2295.0\n",
       "dtype: float64"
      ]
     },
     "metadata": {},
     "execution_count": 77
    }
   ],
   "source": [
    "interval_avg = pd.IntervalIndex(pd.qcut(avg, q=q))\n",
    "interval_avg.right.to_series().reset_index(drop=True).head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "0    1051.162\n",
       "1    1051.162\n",
       "2    1051.162\n",
       "dtype: float64"
      ]
     },
     "metadata": {},
     "execution_count": 78
    }
   ],
   "source": [
    "interval_avg.left.to_series().reset_index(drop=True).head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "0    1243.838\n",
       "1    1243.838\n",
       "2    1243.838\n",
       "dtype: float64"
      ]
     },
     "metadata": {},
     "execution_count": 79
    }
   ],
   "source": [
    "interval_avg.length.to_series().reset_index(drop=True).head(3)"
   ]
  },
  {
   "source": [
    "## 第十章 时序数据\n",
    "### Ex1：太阳辐射数据集\n",
    "#### 1."
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "                     Radiation  Temperature\n",
       "Datetime                                   \n",
       "2016-09-01 00:00:08       2.58           51\n",
       "2016-09-01 00:05:10       2.83           51\n",
       "2016-09-01 00:20:06       2.16           51"
      ],
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>Radiation</th>\n      <th>Temperature</th>\n    </tr>\n    <tr>\n      <th>Datetime</th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>2016-09-01 00:00:08</th>\n      <td>2.58</td>\n      <td>51</td>\n    </tr>\n    <tr>\n      <th>2016-09-01 00:05:10</th>\n      <td>2.83</td>\n      <td>51</td>\n    </tr>\n    <tr>\n      <th>2016-09-01 00:20:06</th>\n      <td>2.16</td>\n      <td>51</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 80
    }
   ],
   "source": [
    "df = pd.read_csv('../data/solar.csv', usecols=['Data','Time','Radiation','Temperature'])\n",
    "solar_date = df.Data.str.extract('([/|\\w]+\\s).+')[0]\n",
    "df['Data'] = pd.to_datetime(solar_date + df.Time)\n",
    "df = df.drop(columns='Time').rename(columns={'Data':'Datetime'}).set_index('Datetime').sort_index()\n",
    "df.head(3)"
   ]
  },
  {
   "source": [
    "#### 2.\n",
    "(a)"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "DatetimeIndex(['2016-09-29 23:55:26', '2016-10-01 00:00:19',\n",
       "               '2016-11-29 19:05:02', '2016-12-01 00:00:02',\n",
       "               '2016-12-05 20:45:53', '2016-12-08 11:10:42'],\n",
       "              dtype='datetime64[ns]', name='Datetime', freq=None)"
      ]
     },
     "metadata": {},
     "execution_count": 81
    }
   ],
   "source": [
    "s = df.index.to_series().reset_index(drop=True).diff().dt.total_seconds()\n",
    "max_3 = s.nlargest(3).index\n",
    "df.index[max_3.union(max_3-1)]"
   ]
  },
  {
   "source": [
    "(b)"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [
    {
     "output_type": "display_data",
     "data": {
      "text/plain": "<Figure size 432x288 with 1 Axes>",
      "image/svg+xml": "<?xml version=\"1.0\" encoding=\"utf-8\" standalone=\"no\"?>\r\n<!DOCTYPE svg PUBLIC \"-//W3C//DTD SVG 1.1//EN\"\r\n  \"http://www.w3.org/Graphics/SVG/1.1/DTD/svg11.dtd\">\r\n<!-- Created with matplotlib (https://matplotlib.org/) -->\r\n<svg height=\"248.518125pt\" version=\"1.1\" viewBox=\"0 0 381.65 248.518125\" width=\"381.65pt\" xmlns=\"http://www.w3.org/2000/svg\" xmlns:xlink=\"http://www.w3.org/1999/xlink\">\r\n <metadata>\r\n  <rdf:RDF xmlns:cc=\"http://creativecommons.org/ns#\" xmlns:dc=\"http://purl.org/dc/elements/1.1/\" xmlns:rdf=\"http://www.w3.org/1999/02/22-rdf-syntax-ns#\">\r\n   <cc:Work>\r\n    <dc:type rdf:resource=\"http://purl.org/dc/dcmitype/StillImage\"/>\r\n    <dc:date>2020-12-29T16:56:12.100970</dc:date>\r\n    <dc:format>image/svg+xml</dc:format>\r\n    <dc:creator>\r\n     <cc:Agent>\r\n      <dc:title>Matplotlib v3.3.2, https://matplotlib.org/</dc:title>\r\n     </cc:Agent>\r\n    </dc:creator>\r\n   </cc:Work>\r\n  </rdf:RDF>\r\n </metadata>\r\n <defs>\r\n  <style type=\"text/css\">*{stroke-linecap:butt;stroke-linejoin:round;}</style>\r\n </defs>\r\n <g id=\"figure_1\">\r\n  <g id=\"patch_1\">\r\n   <path d=\"M -0 248.518125 \r\nL 381.65 248.518125 \r\nL 381.65 0 \r\nL -0 0 \r\nz\r\n\" style=\"fill:none;\"/>\r\n  </g>\r\n  <g id=\"axes_1\">\r\n   <g id=\"patch_2\">\r\n    <path d=\"M 39.65 224.64 \r\nL 374.45 224.64 \r\nL 374.45 7.2 \r\nL 39.65 7.2 \r\nz\r\n\" style=\"fill:#ffffff;\"/>\r\n   </g>\r\n   <g id=\"patch_3\">\r\n    <path clip-path=\"url(#pa8280c6d3f)\" d=\"M 54.868182 224.64 \r\nL 60.955455 224.64 \r\nL 60.955455 221.464642 \r\nL 54.868182 221.464642 \r\nz\r\n\" style=\"fill:#1f77b4;\"/>\r\n   </g>\r\n   <g id=\"patch_4\">\r\n    <path clip-path=\"url(#pa8280c6d3f)\" d=\"M 60.955455 224.64 \r\nL 67.042727 224.64 \r\nL 67.042727 221.62832 \r\nL 60.955455 221.62832 \r\nz\r\n\" style=\"fill:#1f77b4;\"/>\r\n   </g>\r\n   <g id=\"patch_5\">\r\n    <path clip-path=\"url(#pa8280c6d3f)\" d=\"M 67.042727 224.64 \r\nL 73.13 224.64 \r\nL 73.13 220.940872 \r\nL 67.042727 220.940872 \r\nz\r\n\" style=\"fill:#1f77b4;\"/>\r\n   </g>\r\n   <g id=\"patch_6\">\r\n    <path clip-path=\"url(#pa8280c6d3f)\" d=\"M 73.13 224.64 \r\nL 79.217273 224.64 \r\nL 79.217273 217.012594 \r\nL 73.13 217.012594 \r\nz\r\n\" style=\"fill:#1f77b4;\"/>\r\n   </g>\r\n   <g id=\"patch_7\">\r\n    <path clip-path=\"url(#pa8280c6d3f)\" d=\"M 79.217273 224.64 \r\nL 85.304545 224.64 \r\nL 85.304545 211.283855 \r\nL 79.217273 211.283855 \r\nz\r\n\" style=\"fill:#1f77b4;\"/>\r\n   </g>\r\n   <g id=\"patch_8\">\r\n    <path clip-path=\"url(#pa8280c6d3f)\" d=\"M 85.304545 224.64 \r\nL 91.391818 224.64 \r\nL 91.391818 216.81618 \r\nL 85.304545 216.81618 \r\nz\r\n\" style=\"fill:#1f77b4;\"/>\r\n   </g>\r\n   <g id=\"patch_9\">\r\n    <path clip-path=\"url(#pa8280c6d3f)\" d=\"M 91.391818 224.64 \r\nL 97.479091 224.64 \r\nL 97.479091 219.205882 \r\nL 91.391818 219.205882 \r\nz\r\n\" style=\"fill:#1f77b4;\"/>\r\n   </g>\r\n   <g id=\"patch_10\">\r\n    <path clip-path=\"url(#pa8280c6d3f)\" d=\"M 97.479091 224.64 \r\nL 103.566364 224.64 \r\nL 103.566364 217.503629 \r\nL 97.479091 217.503629 \r\nz\r\n\" style=\"fill:#1f77b4;\"/>\r\n   </g>\r\n   <g id=\"patch_11\">\r\n    <path clip-path=\"url(#pa8280c6d3f)\" d=\"M 103.566364 224.64 \r\nL 109.653636 224.64 \r\nL 109.653636 211.087441 \r\nL 103.566364 211.087441 \r\nz\r\n\" style=\"fill:#1f77b4;\"/>\r\n   </g>\r\n   <g id=\"patch_12\">\r\n    <path clip-path=\"url(#pa8280c6d3f)\" d=\"M 109.653636 224.64 \r\nL 115.740909 224.64 \r\nL 115.740909 205.096818 \r\nL 109.653636 205.096818 \r\nz\r\n\" style=\"fill:#1f77b4;\"/>\r\n   </g>\r\n   <g id=\"patch_13\">\r\n    <path clip-path=\"url(#pa8280c6d3f)\" d=\"M 115.740909 224.64 \r\nL 121.828182 224.64 \r\nL 121.828182 188.8272 \r\nL 115.740909 188.8272 \r\nz\r\n\" style=\"fill:#1f77b4;\"/>\r\n   </g>\r\n   <g id=\"patch_14\">\r\n    <path clip-path=\"url(#pa8280c6d3f)\" d=\"M 121.828182 224.64 \r\nL 127.915455 224.64 \r\nL 127.915455 155.50231 \r\nL 121.828182 155.50231 \r\nz\r\n\" style=\"fill:#1f77b4;\"/>\r\n   </g>\r\n   <g id=\"patch_15\">\r\n    <path clip-path=\"url(#pa8280c6d3f)\" d=\"M 127.915455 224.64 \r\nL 134.002727 224.64 \r\nL 134.002727 137.072139 \r\nL 127.915455 137.072139 \r\nz\r\n\" style=\"fill:#1f77b4;\"/>\r\n   </g>\r\n   <g id=\"patch_16\">\r\n    <path clip-path=\"url(#pa8280c6d3f)\" d=\"M 134.002727 224.64 \r\nL 140.09 224.64 \r\nL 140.09 83.320204 \r\nL 134.002727 83.320204 \r\nz\r\n\" style=\"fill:#1f77b4;\"/>\r\n   </g>\r\n   <g id=\"patch_17\">\r\n    <path clip-path=\"url(#pa8280c6d3f)\" d=\"M 140.09 224.64 \r\nL 146.177273 224.64 \r\nL 146.177273 17.554286 \r\nL 140.09 17.554286 \r\nz\r\n\" style=\"fill:#1f77b4;\"/>\r\n   </g>\r\n   <g id=\"patch_18\">\r\n    <path clip-path=\"url(#pa8280c6d3f)\" d=\"M 146.177273 224.64 \r\nL 152.264545 224.64 \r\nL 152.264545 85.120665 \r\nL 146.177273 85.120665 \r\nz\r\n\" style=\"fill:#1f77b4;\"/>\r\n   </g>\r\n   <g id=\"patch_19\">\r\n    <path clip-path=\"url(#pa8280c6d3f)\" d=\"M 152.264545 224.64 \r\nL 158.351818 224.64 \r\nL 158.351818 138.414301 \r\nL 152.264545 138.414301 \r\nz\r\n\" style=\"fill:#1f77b4;\"/>\r\n   </g>\r\n   <g id=\"patch_20\">\r\n    <path clip-path=\"url(#pa8280c6d3f)\" d=\"M 158.351818 224.64 \r\nL 164.439091 224.64 \r\nL 164.439091 156.386172 \r\nL 158.351818 156.386172 \r\nz\r\n\" style=\"fill:#1f77b4;\"/>\r\n   </g>\r\n   <g id=\"patch_21\">\r\n    <path clip-path=\"url(#pa8280c6d3f)\" d=\"M 164.439091 224.64 \r\nL 170.526364 224.64 \r\nL 170.526364 189.383706 \r\nL 164.439091 189.383706 \r\nz\r\n\" style=\"fill:#1f77b4;\"/>\r\n   </g>\r\n   <g id=\"patch_22\">\r\n    <path clip-path=\"url(#pa8280c6d3f)\" d=\"M 170.526364 224.64 \r\nL 176.613636 224.64 \r\nL 176.613636 203.918334 \r\nL 170.526364 203.918334 \r\nz\r\n\" style=\"fill:#1f77b4;\"/>\r\n   </g>\r\n   <g id=\"patch_23\">\r\n    <path clip-path=\"url(#pa8280c6d3f)\" d=\"M 176.613636 224.64 \r\nL 182.700909 224.64 \r\nL 182.700909 210.923763 \r\nL 176.613636 210.923763 \r\nz\r\n\" style=\"fill:#1f77b4;\"/>\r\n   </g>\r\n   <g id=\"patch_24\">\r\n    <path clip-path=\"url(#pa8280c6d3f)\" d=\"M 182.700909 224.64 \r\nL 188.788182 224.64 \r\nL 188.788182 215.83411 \r\nL 182.700909 215.83411 \r\nz\r\n\" style=\"fill:#1f77b4;\"/>\r\n   </g>\r\n   <g id=\"patch_25\">\r\n    <path clip-path=\"url(#pa8280c6d3f)\" d=\"M 188.788182 224.64 \r\nL 194.875455 224.64 \r\nL 194.875455 219.500503 \r\nL 188.788182 219.500503 \r\nz\r\n\" style=\"fill:#1f77b4;\"/>\r\n   </g>\r\n   <g id=\"patch_26\">\r\n    <path clip-path=\"url(#pa8280c6d3f)\" d=\"M 194.875455 224.64 \r\nL 200.962727 224.64 \r\nL 200.962727 216.979858 \r\nL 194.875455 216.979858 \r\nz\r\n\" style=\"fill:#1f77b4;\"/>\r\n   </g>\r\n   <g id=\"patch_27\">\r\n    <path clip-path=\"url(#pa8280c6d3f)\" d=\"M 200.962727 224.64 \r\nL 207.05 224.64 \r\nL 207.05 213.084316 \r\nL 200.962727 213.084316 \r\nz\r\n\" style=\"fill:#1f77b4;\"/>\r\n   </g>\r\n   <g id=\"patch_28\">\r\n    <path clip-path=\"url(#pa8280c6d3f)\" d=\"M 207.05 224.64 \r\nL 213.137273 224.64 \r\nL 213.137273 213.477144 \r\nL 207.05 213.477144 \r\nz\r\n\" style=\"fill:#1f77b4;\"/>\r\n   </g>\r\n   <g id=\"patch_29\">\r\n    <path clip-path=\"url(#pa8280c6d3f)\" d=\"M 213.137273 224.64 \r\nL 219.224545 224.64 \r\nL 219.224545 221.824734 \r\nL 213.137273 221.824734 \r\nz\r\n\" style=\"fill:#1f77b4;\"/>\r\n   </g>\r\n   <g id=\"patch_30\">\r\n    <path clip-path=\"url(#pa8280c6d3f)\" d=\"M 219.224545 224.64 \r\nL 225.311818 224.64 \r\nL 225.311818 223.068689 \r\nL 219.224545 223.068689 \r\nz\r\n\" style=\"fill:#1f77b4;\"/>\r\n   </g>\r\n   <g id=\"patch_31\">\r\n    <path clip-path=\"url(#pa8280c6d3f)\" d=\"M 225.311818 224.64 \r\nL 231.399091 224.64 \r\nL 231.399091 223.232367 \r\nL 225.311818 223.232367 \r\nz\r\n\" style=\"fill:#1f77b4;\"/>\r\n   </g>\r\n   <g id=\"patch_32\">\r\n    <path clip-path=\"url(#pa8280c6d3f)\" d=\"M 231.399091 224.64 \r\nL 237.486364 224.64 \r\nL 237.486364 222.61039 \r\nL 231.399091 222.61039 \r\nz\r\n\" style=\"fill:#1f77b4;\"/>\r\n   </g>\r\n   <g id=\"patch_33\">\r\n    <path clip-path=\"url(#pa8280c6d3f)\" d=\"M 237.486364 224.64 \r\nL 243.573636 224.64 \r\nL 243.573636 223.494252 \r\nL 237.486364 223.494252 \r\nz\r\n\" style=\"fill:#1f77b4;\"/>\r\n   </g>\r\n   <g id=\"patch_34\">\r\n    <path clip-path=\"url(#pa8280c6d3f)\" d=\"M 243.573636 224.64 \r\nL 249.660909 224.64 \r\nL 249.660909 223.330574 \r\nL 243.573636 223.330574 \r\nz\r\n\" style=\"fill:#1f77b4;\"/>\r\n   </g>\r\n   <g id=\"patch_35\">\r\n    <path clip-path=\"url(#pa8280c6d3f)\" d=\"M 249.660909 224.64 \r\nL 255.748182 224.64 \r\nL 255.748182 223.625195 \r\nL 249.660909 223.625195 \r\nz\r\n\" style=\"fill:#1f77b4;\"/>\r\n   </g>\r\n   <g id=\"patch_36\">\r\n    <path clip-path=\"url(#pa8280c6d3f)\" d=\"M 255.748182 224.64 \r\nL 261.835455 224.64 \r\nL 261.835455 224.050758 \r\nL 255.748182 224.050758 \r\nz\r\n\" style=\"fill:#1f77b4;\"/>\r\n   </g>\r\n   <g id=\"patch_37\">\r\n    <path clip-path=\"url(#pa8280c6d3f)\" d=\"M 261.835455 224.64 \r\nL 267.922727 224.64 \r\nL 267.922727 224.018023 \r\nL 261.835455 224.018023 \r\nz\r\n\" style=\"fill:#1f77b4;\"/>\r\n   </g>\r\n   <g id=\"patch_38\">\r\n    <path clip-path=\"url(#pa8280c6d3f)\" d=\"M 267.922727 224.64 \r\nL 274.01 224.64 \r\nL 274.01 224.148965 \r\nL 267.922727 224.148965 \r\nz\r\n\" style=\"fill:#1f77b4;\"/>\r\n   </g>\r\n   <g id=\"patch_39\">\r\n    <path clip-path=\"url(#pa8280c6d3f)\" d=\"M 274.01 224.64 \r\nL 280.097273 224.64 \r\nL 280.097273 224.214437 \r\nL 274.01 224.214437 \r\nz\r\n\" style=\"fill:#1f77b4;\"/>\r\n   </g>\r\n   <g id=\"patch_40\">\r\n    <path clip-path=\"url(#pa8280c6d3f)\" d=\"M 280.097273 224.64 \r\nL 286.184545 224.64 \r\nL 286.184545 224.345379 \r\nL 280.097273 224.345379 \r\nz\r\n\" style=\"fill:#1f77b4;\"/>\r\n   </g>\r\n   <g id=\"patch_41\">\r\n    <path clip-path=\"url(#pa8280c6d3f)\" d=\"M 286.184545 224.64 \r\nL 292.271818 224.64 \r\nL 292.271818 224.247172 \r\nL 286.184545 224.247172 \r\nz\r\n\" style=\"fill:#1f77b4;\"/>\r\n   </g>\r\n   <g id=\"patch_42\">\r\n    <path clip-path=\"url(#pa8280c6d3f)\" d=\"M 292.271818 224.64 \r\nL 298.359091 224.64 \r\nL 298.359091 224.378115 \r\nL 292.271818 224.378115 \r\nz\r\n\" style=\"fill:#1f77b4;\"/>\r\n   </g>\r\n   <g id=\"patch_43\">\r\n    <path clip-path=\"url(#pa8280c6d3f)\" d=\"M 298.359091 224.64 \r\nL 304.446364 224.64 \r\nL 304.446364 224.41085 \r\nL 298.359091 224.41085 \r\nz\r\n\" style=\"fill:#1f77b4;\"/>\r\n   </g>\r\n   <g id=\"patch_44\">\r\n    <path clip-path=\"url(#pa8280c6d3f)\" d=\"M 304.446364 224.64 \r\nL 310.533636 224.64 \r\nL 310.533636 224.509057 \r\nL 304.446364 224.509057 \r\nz\r\n\" style=\"fill:#1f77b4;\"/>\r\n   </g>\r\n   <g id=\"patch_45\">\r\n    <path clip-path=\"url(#pa8280c6d3f)\" d=\"M 310.533636 224.64 \r\nL 316.620909 224.64 \r\nL 316.620909 224.541793 \r\nL 310.533636 224.541793 \r\nz\r\n\" style=\"fill:#1f77b4;\"/>\r\n   </g>\r\n   <g id=\"patch_46\">\r\n    <path clip-path=\"url(#pa8280c6d3f)\" d=\"M 316.620909 224.64 \r\nL 322.708182 224.64 \r\nL 322.708182 224.214437 \r\nL 316.620909 224.214437 \r\nz\r\n\" style=\"fill:#1f77b4;\"/>\r\n   </g>\r\n   <g id=\"patch_47\">\r\n    <path clip-path=\"url(#pa8280c6d3f)\" d=\"M 322.708182 224.64 \r\nL 328.795455 224.64 \r\nL 328.795455 224.214437 \r\nL 322.708182 224.214437 \r\nz\r\n\" style=\"fill:#1f77b4;\"/>\r\n   </g>\r\n   <g id=\"patch_48\">\r\n    <path clip-path=\"url(#pa8280c6d3f)\" d=\"M 328.795455 224.64 \r\nL 334.882727 224.64 \r\nL 334.882727 224.574529 \r\nL 328.795455 224.574529 \r\nz\r\n\" style=\"fill:#1f77b4;\"/>\r\n   </g>\r\n   <g id=\"patch_49\">\r\n    <path clip-path=\"url(#pa8280c6d3f)\" d=\"M 334.882727 224.64 \r\nL 340.97 224.64 \r\nL 340.97 224.64 \r\nL 334.882727 224.64 \r\nz\r\n\" style=\"fill:#1f77b4;\"/>\r\n   </g>\r\n   <g id=\"patch_50\">\r\n    <path clip-path=\"url(#pa8280c6d3f)\" d=\"M 340.97 224.64 \r\nL 347.057273 224.64 \r\nL 347.057273 224.607264 \r\nL 340.97 224.607264 \r\nz\r\n\" style=\"fill:#1f77b4;\"/>\r\n   </g>\r\n   <g id=\"patch_51\">\r\n    <path clip-path=\"url(#pa8280c6d3f)\" d=\"M 347.057273 224.64 \r\nL 353.144545 224.64 \r\nL 353.144545 224.607264 \r\nL 347.057273 224.607264 \r\nz\r\n\" style=\"fill:#1f77b4;\"/>\r\n   </g>\r\n   <g id=\"patch_52\">\r\n    <path clip-path=\"url(#pa8280c6d3f)\" d=\"M 353.144545 224.64 \r\nL 359.231818 224.64 \r\nL 359.231818 224.509057 \r\nL 353.144545 224.509057 \r\nz\r\n\" style=\"fill:#1f77b4;\"/>\r\n   </g>\r\n   <g id=\"matplotlib.axis_1\">\r\n    <g id=\"xtick_1\">\r\n     <g id=\"line2d_1\">\r\n      <defs>\r\n       <path d=\"M 0 0 \r\nL 0 3.5 \r\n\" id=\"m803a415f1d\" style=\"stroke:#000000;stroke-width:0.8;\"/>\r\n      </defs>\r\n      <g>\r\n       <use style=\"stroke:#000000;stroke-width:0.8;\" x=\"84.133916\" xlink:href=\"#m803a415f1d\" y=\"224.64\"/>\r\n      </g>\r\n     </g>\r\n     <g id=\"text_1\">\r\n      <!-- 290 -->\r\n      <g transform=\"translate(74.590166 239.238437)scale(0.1 -0.1)\">\r\n       <defs>\r\n        <path d=\"M 19.1875 8.296875 \r\nL 53.609375 8.296875 \r\nL 53.609375 0 \r\nL 7.328125 0 \r\nL 7.328125 8.296875 \r\nQ 12.9375 14.109375 22.625 23.890625 \r\nQ 32.328125 33.6875 34.8125 36.53125 \r\nQ 39.546875 41.84375 41.421875 45.53125 \r\nQ 43.3125 49.21875 43.3125 52.78125 \r\nQ 43.3125 58.59375 39.234375 62.25 \r\nQ 35.15625 65.921875 28.609375 65.921875 \r\nQ 23.96875 65.921875 18.8125 64.3125 \r\nQ 13.671875 62.703125 7.8125 59.421875 \r\nL 7.8125 69.390625 \r\nQ 13.765625 71.78125 18.9375 73 \r\nQ 24.125 74.21875 28.421875 74.21875 \r\nQ 39.75 74.21875 46.484375 68.546875 \r\nQ 53.21875 62.890625 53.21875 53.421875 \r\nQ 53.21875 48.921875 51.53125 44.890625 \r\nQ 49.859375 40.875 45.40625 35.40625 \r\nQ 44.1875 33.984375 37.640625 27.21875 \r\nQ 31.109375 20.453125 19.1875 8.296875 \r\nz\r\n\" id=\"DejaVuSans-50\"/>\r\n        <path d=\"M 10.984375 1.515625 \r\nL 10.984375 10.5 \r\nQ 14.703125 8.734375 18.5 7.8125 \r\nQ 22.3125 6.890625 25.984375 6.890625 \r\nQ 35.75 6.890625 40.890625 13.453125 \r\nQ 46.046875 20.015625 46.78125 33.40625 \r\nQ 43.953125 29.203125 39.59375 26.953125 \r\nQ 35.25 24.703125 29.984375 24.703125 \r\nQ 19.046875 24.703125 12.671875 31.3125 \r\nQ 6.296875 37.9375 6.296875 49.421875 \r\nQ 6.296875 60.640625 12.9375 67.421875 \r\nQ 19.578125 74.21875 30.609375 74.21875 \r\nQ 43.265625 74.21875 49.921875 64.515625 \r\nQ 56.59375 54.828125 56.59375 36.375 \r\nQ 56.59375 19.140625 48.40625 8.859375 \r\nQ 40.234375 -1.421875 26.421875 -1.421875 \r\nQ 22.703125 -1.421875 18.890625 -0.6875 \r\nQ 15.09375 0.046875 10.984375 1.515625 \r\nz\r\nM 30.609375 32.421875 \r\nQ 37.25 32.421875 41.125 36.953125 \r\nQ 45.015625 41.5 45.015625 49.421875 \r\nQ 45.015625 57.28125 41.125 61.84375 \r\nQ 37.25 66.40625 30.609375 66.40625 \r\nQ 23.96875 66.40625 20.09375 61.84375 \r\nQ 16.21875 57.28125 16.21875 49.421875 \r\nQ 16.21875 41.5 20.09375 36.953125 \r\nQ 23.96875 32.421875 30.609375 32.421875 \r\nz\r\n\" id=\"DejaVuSans-57\"/>\r\n        <path d=\"M 31.78125 66.40625 \r\nQ 24.171875 66.40625 20.328125 58.90625 \r\nQ 16.5 51.421875 16.5 36.375 \r\nQ 16.5 21.390625 20.328125 13.890625 \r\nQ 24.171875 6.390625 31.78125 6.390625 \r\nQ 39.453125 6.390625 43.28125 13.890625 \r\nQ 47.125 21.390625 47.125 36.375 \r\nQ 47.125 51.421875 43.28125 58.90625 \r\nQ 39.453125 66.40625 31.78125 66.40625 \r\nz\r\nM 31.78125 74.21875 \r\nQ 44.046875 74.21875 50.515625 64.515625 \r\nQ 56.984375 54.828125 56.984375 36.375 \r\nQ 56.984375 17.96875 50.515625 8.265625 \r\nQ 44.046875 -1.421875 31.78125 -1.421875 \r\nQ 19.53125 -1.421875 13.0625 8.265625 \r\nQ 6.59375 17.96875 6.59375 36.375 \r\nQ 6.59375 54.828125 13.0625 64.515625 \r\nQ 19.53125 74.21875 31.78125 74.21875 \r\nz\r\n\" id=\"DejaVuSans-48\"/>\r\n       </defs>\r\n       <use xlink:href=\"#DejaVuSans-50\"/>\r\n       <use x=\"63.623047\" xlink:href=\"#DejaVuSans-57\"/>\r\n       <use x=\"127.246094\" xlink:href=\"#DejaVuSans-48\"/>\r\n      </g>\r\n     </g>\r\n    </g>\r\n    <g id=\"xtick_2\">\r\n     <g id=\"line2d_2\">\r\n      <g>\r\n       <use style=\"stroke:#000000;stroke-width:0.8;\" x=\"142.665385\" xlink:href=\"#m803a415f1d\" y=\"224.64\"/>\r\n      </g>\r\n     </g>\r\n     <g id=\"text_2\">\r\n      <!-- 300 -->\r\n      <g transform=\"translate(133.121635 239.238437)scale(0.1 -0.1)\">\r\n       <defs>\r\n        <path d=\"M 40.578125 39.3125 \r\nQ 47.65625 37.796875 51.625 33 \r\nQ 55.609375 28.21875 55.609375 21.1875 \r\nQ 55.609375 10.40625 48.1875 4.484375 \r\nQ 40.765625 -1.421875 27.09375 -1.421875 \r\nQ 22.515625 -1.421875 17.65625 -0.515625 \r\nQ 12.796875 0.390625 7.625 2.203125 \r\nL 7.625 11.71875 \r\nQ 11.71875 9.328125 16.59375 8.109375 \r\nQ 21.484375 6.890625 26.8125 6.890625 \r\nQ 36.078125 6.890625 40.9375 10.546875 \r\nQ 45.796875 14.203125 45.796875 21.1875 \r\nQ 45.796875 27.640625 41.28125 31.265625 \r\nQ 36.765625 34.90625 28.71875 34.90625 \r\nL 20.21875 34.90625 \r\nL 20.21875 43.015625 \r\nL 29.109375 43.015625 \r\nQ 36.375 43.015625 40.234375 45.921875 \r\nQ 44.09375 48.828125 44.09375 54.296875 \r\nQ 44.09375 59.90625 40.109375 62.90625 \r\nQ 36.140625 65.921875 28.71875 65.921875 \r\nQ 24.65625 65.921875 20.015625 65.03125 \r\nQ 15.375 64.15625 9.8125 62.3125 \r\nL 9.8125 71.09375 \r\nQ 15.4375 72.65625 20.34375 73.4375 \r\nQ 25.25 74.21875 29.59375 74.21875 \r\nQ 40.828125 74.21875 47.359375 69.109375 \r\nQ 53.90625 64.015625 53.90625 55.328125 \r\nQ 53.90625 49.265625 50.4375 45.09375 \r\nQ 46.96875 40.921875 40.578125 39.3125 \r\nz\r\n\" id=\"DejaVuSans-51\"/>\r\n       </defs>\r\n       <use xlink:href=\"#DejaVuSans-51\"/>\r\n       <use x=\"63.623047\" xlink:href=\"#DejaVuSans-48\"/>\r\n       <use x=\"127.246094\" xlink:href=\"#DejaVuSans-48\"/>\r\n      </g>\r\n     </g>\r\n    </g>\r\n    <g id=\"xtick_3\">\r\n     <g id=\"line2d_3\">\r\n      <g>\r\n       <use style=\"stroke:#000000;stroke-width:0.8;\" x=\"201.196853\" xlink:href=\"#m803a415f1d\" y=\"224.64\"/>\r\n      </g>\r\n     </g>\r\n     <g id=\"text_3\">\r\n      <!-- 310 -->\r\n      <g transform=\"translate(191.653103 239.238437)scale(0.1 -0.1)\">\r\n       <defs>\r\n        <path d=\"M 12.40625 8.296875 \r\nL 28.515625 8.296875 \r\nL 28.515625 63.921875 \r\nL 10.984375 60.40625 \r\nL 10.984375 69.390625 \r\nL 28.421875 72.90625 \r\nL 38.28125 72.90625 \r\nL 38.28125 8.296875 \r\nL 54.390625 8.296875 \r\nL 54.390625 0 \r\nL 12.40625 0 \r\nz\r\n\" id=\"DejaVuSans-49\"/>\r\n       </defs>\r\n       <use xlink:href=\"#DejaVuSans-51\"/>\r\n       <use x=\"63.623047\" xlink:href=\"#DejaVuSans-49\"/>\r\n       <use x=\"127.246094\" xlink:href=\"#DejaVuSans-48\"/>\r\n      </g>\r\n     </g>\r\n    </g>\r\n    <g id=\"xtick_4\">\r\n     <g id=\"line2d_4\">\r\n      <g>\r\n       <use style=\"stroke:#000000;stroke-width:0.8;\" x=\"259.728322\" xlink:href=\"#m803a415f1d\" y=\"224.64\"/>\r\n      </g>\r\n     </g>\r\n     <g id=\"text_4\">\r\n      <!-- 320 -->\r\n      <g transform=\"translate(250.184572 239.238437)scale(0.1 -0.1)\">\r\n       <use xlink:href=\"#DejaVuSans-51\"/>\r\n       <use x=\"63.623047\" xlink:href=\"#DejaVuSans-50\"/>\r\n       <use x=\"127.246094\" xlink:href=\"#DejaVuSans-48\"/>\r\n      </g>\r\n     </g>\r\n    </g>\r\n    <g id=\"xtick_5\">\r\n     <g id=\"line2d_5\">\r\n      <g>\r\n       <use style=\"stroke:#000000;stroke-width:0.8;\" x=\"318.25979\" xlink:href=\"#m803a415f1d\" y=\"224.64\"/>\r\n      </g>\r\n     </g>\r\n     <g id=\"text_5\">\r\n      <!-- 330 -->\r\n      <g transform=\"translate(308.71604 239.238437)scale(0.1 -0.1)\">\r\n       <use xlink:href=\"#DejaVuSans-51\"/>\r\n       <use x=\"63.623047\" xlink:href=\"#DejaVuSans-51\"/>\r\n       <use x=\"127.246094\" xlink:href=\"#DejaVuSans-48\"/>\r\n      </g>\r\n     </g>\r\n    </g>\r\n   </g>\r\n   <g id=\"matplotlib.axis_2\">\r\n    <g id=\"ytick_1\">\r\n     <g id=\"line2d_6\">\r\n      <defs>\r\n       <path d=\"M 0 0 \r\nL -3.5 0 \r\n\" id=\"ma2e8881b37\" style=\"stroke:#000000;stroke-width:0.8;\"/>\r\n      </defs>\r\n      <g>\r\n       <use style=\"stroke:#000000;stroke-width:0.8;\" x=\"39.65\" xlink:href=\"#ma2e8881b37\" y=\"224.64\"/>\r\n      </g>\r\n     </g>\r\n     <g id=\"text_6\">\r\n      <!-- 0 -->\r\n      <g transform=\"translate(26.2875 228.439219)scale(0.1 -0.1)\">\r\n       <use xlink:href=\"#DejaVuSans-48\"/>\r\n      </g>\r\n     </g>\r\n    </g>\r\n    <g id=\"ytick_2\">\r\n     <g id=\"line2d_7\">\r\n      <g>\r\n       <use style=\"stroke:#000000;stroke-width:0.8;\" x=\"39.65\" xlink:href=\"#ma2e8881b37\" y=\"191.904351\"/>\r\n      </g>\r\n     </g>\r\n     <g id=\"text_7\">\r\n      <!-- 1000 -->\r\n      <g transform=\"translate(7.2 195.70357)scale(0.1 -0.1)\">\r\n       <use xlink:href=\"#DejaVuSans-49\"/>\r\n       <use x=\"63.623047\" xlink:href=\"#DejaVuSans-48\"/>\r\n       <use x=\"127.246094\" xlink:href=\"#DejaVuSans-48\"/>\r\n       <use x=\"190.869141\" xlink:href=\"#DejaVuSans-48\"/>\r\n      </g>\r\n     </g>\r\n    </g>\r\n    <g id=\"ytick_3\">\r\n     <g id=\"line2d_8\">\r\n      <g>\r\n       <use style=\"stroke:#000000;stroke-width:0.8;\" x=\"39.65\" xlink:href=\"#ma2e8881b37\" y=\"159.168702\"/>\r\n      </g>\r\n     </g>\r\n     <g id=\"text_8\">\r\n      <!-- 2000 -->\r\n      <g transform=\"translate(7.2 162.967921)scale(0.1 -0.1)\">\r\n       <use xlink:href=\"#DejaVuSans-50\"/>\r\n       <use x=\"63.623047\" xlink:href=\"#DejaVuSans-48\"/>\r\n       <use x=\"127.246094\" xlink:href=\"#DejaVuSans-48\"/>\r\n       <use x=\"190.869141\" xlink:href=\"#DejaVuSans-48\"/>\r\n      </g>\r\n     </g>\r\n    </g>\r\n    <g id=\"ytick_4\">\r\n     <g id=\"line2d_9\">\r\n      <g>\r\n       <use style=\"stroke:#000000;stroke-width:0.8;\" x=\"39.65\" xlink:href=\"#ma2e8881b37\" y=\"126.433054\"/>\r\n      </g>\r\n     </g>\r\n     <g id=\"text_9\">\r\n      <!-- 3000 -->\r\n      <g transform=\"translate(7.2 130.232272)scale(0.1 -0.1)\">\r\n       <use xlink:href=\"#DejaVuSans-51\"/>\r\n       <use x=\"63.623047\" xlink:href=\"#DejaVuSans-48\"/>\r\n       <use x=\"127.246094\" xlink:href=\"#DejaVuSans-48\"/>\r\n       <use x=\"190.869141\" xlink:href=\"#DejaVuSans-48\"/>\r\n      </g>\r\n     </g>\r\n    </g>\r\n    <g id=\"ytick_5\">\r\n     <g id=\"line2d_10\">\r\n      <g>\r\n       <use style=\"stroke:#000000;stroke-width:0.8;\" x=\"39.65\" xlink:href=\"#ma2e8881b37\" y=\"93.697405\"/>\r\n      </g>\r\n     </g>\r\n     <g id=\"text_10\">\r\n      <!-- 4000 -->\r\n      <g transform=\"translate(7.2 97.496624)scale(0.1 -0.1)\">\r\n       <defs>\r\n        <path d=\"M 37.796875 64.3125 \r\nL 12.890625 25.390625 \r\nL 37.796875 25.390625 \r\nz\r\nM 35.203125 72.90625 \r\nL 47.609375 72.90625 \r\nL 47.609375 25.390625 \r\nL 58.015625 25.390625 \r\nL 58.015625 17.1875 \r\nL 47.609375 17.1875 \r\nL 47.609375 0 \r\nL 37.796875 0 \r\nL 37.796875 17.1875 \r\nL 4.890625 17.1875 \r\nL 4.890625 26.703125 \r\nz\r\n\" id=\"DejaVuSans-52\"/>\r\n       </defs>\r\n       <use xlink:href=\"#DejaVuSans-52\"/>\r\n       <use x=\"63.623047\" xlink:href=\"#DejaVuSans-48\"/>\r\n       <use x=\"127.246094\" xlink:href=\"#DejaVuSans-48\"/>\r\n       <use x=\"190.869141\" xlink:href=\"#DejaVuSans-48\"/>\r\n      </g>\r\n     </g>\r\n    </g>\r\n    <g id=\"ytick_6\">\r\n     <g id=\"line2d_11\">\r\n      <g>\r\n       <use style=\"stroke:#000000;stroke-width:0.8;\" x=\"39.65\" xlink:href=\"#ma2e8881b37\" y=\"60.961756\"/>\r\n      </g>\r\n     </g>\r\n     <g id=\"text_11\">\r\n      <!-- 5000 -->\r\n      <g transform=\"translate(7.2 64.760975)scale(0.1 -0.1)\">\r\n       <defs>\r\n        <path d=\"M 10.796875 72.90625 \r\nL 49.515625 72.90625 \r\nL 49.515625 64.59375 \r\nL 19.828125 64.59375 \r\nL 19.828125 46.734375 \r\nQ 21.96875 47.46875 24.109375 47.828125 \r\nQ 26.265625 48.1875 28.421875 48.1875 \r\nQ 40.625 48.1875 47.75 41.5 \r\nQ 54.890625 34.8125 54.890625 23.390625 \r\nQ 54.890625 11.625 47.5625 5.09375 \r\nQ 40.234375 -1.421875 26.90625 -1.421875 \r\nQ 22.3125 -1.421875 17.546875 -0.640625 \r\nQ 12.796875 0.140625 7.71875 1.703125 \r\nL 7.71875 11.625 \r\nQ 12.109375 9.234375 16.796875 8.0625 \r\nQ 21.484375 6.890625 26.703125 6.890625 \r\nQ 35.15625 6.890625 40.078125 11.328125 \r\nQ 45.015625 15.765625 45.015625 23.390625 \r\nQ 45.015625 31 40.078125 35.4375 \r\nQ 35.15625 39.890625 26.703125 39.890625 \r\nQ 22.75 39.890625 18.8125 39.015625 \r\nQ 14.890625 38.140625 10.796875 36.28125 \r\nz\r\n\" id=\"DejaVuSans-53\"/>\r\n       </defs>\r\n       <use xlink:href=\"#DejaVuSans-53\"/>\r\n       <use x=\"63.623047\" xlink:href=\"#DejaVuSans-48\"/>\r\n       <use x=\"127.246094\" xlink:href=\"#DejaVuSans-48\"/>\r\n       <use x=\"190.869141\" xlink:href=\"#DejaVuSans-48\"/>\r\n      </g>\r\n     </g>\r\n    </g>\r\n    <g id=\"ytick_7\">\r\n     <g id=\"line2d_12\">\r\n      <g>\r\n       <use style=\"stroke:#000000;stroke-width:0.8;\" x=\"39.65\" xlink:href=\"#ma2e8881b37\" y=\"28.226107\"/>\r\n      </g>\r\n     </g>\r\n     <g id=\"text_12\">\r\n      <!-- 6000 -->\r\n      <g transform=\"translate(7.2 32.025326)scale(0.1 -0.1)\">\r\n       <defs>\r\n        <path d=\"M 33.015625 40.375 \r\nQ 26.375 40.375 22.484375 35.828125 \r\nQ 18.609375 31.296875 18.609375 23.390625 \r\nQ 18.609375 15.53125 22.484375 10.953125 \r\nQ 26.375 6.390625 33.015625 6.390625 \r\nQ 39.65625 6.390625 43.53125 10.953125 \r\nQ 47.40625 15.53125 47.40625 23.390625 \r\nQ 47.40625 31.296875 43.53125 35.828125 \r\nQ 39.65625 40.375 33.015625 40.375 \r\nz\r\nM 52.59375 71.296875 \r\nL 52.59375 62.3125 \r\nQ 48.875 64.0625 45.09375 64.984375 \r\nQ 41.3125 65.921875 37.59375 65.921875 \r\nQ 27.828125 65.921875 22.671875 59.328125 \r\nQ 17.53125 52.734375 16.796875 39.40625 \r\nQ 19.671875 43.65625 24.015625 45.921875 \r\nQ 28.375 48.1875 33.59375 48.1875 \r\nQ 44.578125 48.1875 50.953125 41.515625 \r\nQ 57.328125 34.859375 57.328125 23.390625 \r\nQ 57.328125 12.15625 50.6875 5.359375 \r\nQ 44.046875 -1.421875 33.015625 -1.421875 \r\nQ 20.359375 -1.421875 13.671875 8.265625 \r\nQ 6.984375 17.96875 6.984375 36.375 \r\nQ 6.984375 53.65625 15.1875 63.9375 \r\nQ 23.390625 74.21875 37.203125 74.21875 \r\nQ 40.921875 74.21875 44.703125 73.484375 \r\nQ 48.484375 72.75 52.59375 71.296875 \r\nz\r\n\" id=\"DejaVuSans-54\"/>\r\n       </defs>\r\n       <use xlink:href=\"#DejaVuSans-54\"/>\r\n       <use x=\"63.623047\" xlink:href=\"#DejaVuSans-48\"/>\r\n       <use x=\"127.246094\" xlink:href=\"#DejaVuSans-48\"/>\r\n       <use x=\"190.869141\" xlink:href=\"#DejaVuSans-48\"/>\r\n      </g>\r\n     </g>\r\n    </g>\r\n   </g>\r\n   <g id=\"patch_53\">\r\n    <path d=\"M 39.65 224.64 \r\nL 39.65 7.2 \r\n\" style=\"fill:none;stroke:#000000;stroke-linecap:square;stroke-linejoin:miter;stroke-width:0.8;\"/>\r\n   </g>\r\n   <g id=\"patch_54\">\r\n    <path d=\"M 374.45 224.64 \r\nL 374.45 7.2 \r\n\" style=\"fill:none;stroke:#000000;stroke-linecap:square;stroke-linejoin:miter;stroke-width:0.8;\"/>\r\n   </g>\r\n   <g id=\"patch_55\">\r\n    <path d=\"M 39.65 224.64 \r\nL 374.45 224.64 \r\n\" style=\"fill:none;stroke:#000000;stroke-linecap:square;stroke-linejoin:miter;stroke-width:0.8;\"/>\r\n   </g>\r\n   <g id=\"patch_56\">\r\n    <path d=\"M 39.65 7.2 \r\nL 374.45 7.2 \r\n\" style=\"fill:none;stroke:#000000;stroke-linecap:square;stroke-linejoin:miter;stroke-width:0.8;\"/>\r\n   </g>\r\n  </g>\r\n </g>\r\n <defs>\r\n  <clipPath id=\"pa8280c6d3f\">\r\n   <rect height=\"217.44\" width=\"334.8\" x=\"39.65\" y=\"7.2\"/>\r\n  </clipPath>\r\n </defs>\r\n</svg>\r\n",
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAX0AAAD4CAYAAAAAczaOAAAAOXRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjMuMiwgaHR0cHM6Ly9tYXRwbG90bGliLm9yZy8vihELAAAACXBIWXMAAAsTAAALEwEAmpwYAAASjElEQVR4nO3df6jd933f8ecrsuuYJCI2vvIUSUwaqOlsQ5L6onkEuh/uYjGHyGO4aHS1tghEjdqkUGjktrSUIlA2KEu2xUUkrWWW1hVNgtW4aqJqC2WgWLlOnbqy7FqLXUtIs27ThjoruEh574/zCT5I59577vXVka4+zwccvt/zPp/v0edtmdf56nO+55xUFZKkPrztak9AkjQ5hr4kdcTQl6SOGPqS1BFDX5I6csPVnsBCbrvtttq4cePVnoYkrSjPPPPMX1XV1KX1az70N27cyMzMzNWehiStKEn+clTd5R1J6oihL0kdMfQlqSOGviR1xNCXpI4Y+pLUEUNfkjpi6EtSRwx9SerINf+JXF1bNu55amT9lX33T3gmkpbCM31J6oihL0kdMfQlqSOGviR1xNCXpI4Y+pLUkbFCP8m7k/x+kheSnEzyT5PcmuRIkpfa9pah8Y8kOZXkxST3DdXvTvJce+zTSXIlmpIkjTbumf6ngD+qqh8B3gecBPYAR6tqM3C03SfJHcB24E5gK/CZJKva8zwK7AI2t9vWZepDkjSGBUM/yWrgx4DPAVTV31fVd4FtwIE27ADwQNvfBjxRVW9U1cvAKWBLkrXA6qo6VlUFPD50jCRpAsY50/9HwCzw20n+NMlnk7wDuL2qzgG07Zo2fh1weuj4M622ru1fWr9Mkl1JZpLMzM7OLqohSdLcxgn9G4AfBR6tqg8A/4+2lDOHUev0NU/98mLV/qqarqrpqanLfsxdkrRE44T+GeBMVT3d7v8+gxeB19qSDW17fmj8hqHj1wNnW339iLokaUIWDP2q+r/A6STvbaV7geeBQ8COVtsBPNn2DwHbk9yUZBODN2yPtyWg15Pc067aeWjoGEnSBIz7LZs/C3w+yQ8B3wb+I4MXjINJdgKvAg8CVNWJJAcZvDBcAHZX1cX2PA8DjwE3A4fbTZI0IWOFflU9C0yPeOjeOcbvBfaOqM8Ady1ifpKkZeQnciWpI4a+JHXE0Jekjhj6ktQRQ1+SOmLoS1JHDH1J6oihL0kdMfQlqSOGviR1xNCXpI4Y+pLUEUNfkjpi6EtSRwx9SeqIoS9JHTH0Jakjhr4kdcTQl6SOGPqS1BFDX5I6YuhLUkcMfUnqiKEvSR0ZK/STvJLkuSTPJplptVuTHEnyUtveMjT+kSSnkryY5L6h+t3teU4l+XSSLH9LkqS5LOZM/19U1furarrd3wMcrarNwNF2nyR3ANuBO4GtwGeSrGrHPArsAja329a33oIkaVxvZXlnG3Cg7R8AHhiqP1FVb1TVy8ApYEuStcDqqjpWVQU8PnSMJGkCxg39Ar6a5Jkku1rt9qo6B9C2a1p9HXB66Ngzrbau7V9av0ySXUlmkszMzs6OOUVJ0kJuGHPcB6vqbJI1wJEkL8wzdtQ6fc1Tv7xYtR/YDzA9PT1yjCRp8cY606+qs217HvgSsAV4rS3Z0Lbn2/AzwIahw9cDZ1t9/Yi6JGlCFgz9JO9I8q4f7AMfAv4cOATsaMN2AE+2/UPA9iQ3JdnE4A3b420J6PUk97Srdh4aOkaSNAHjLO/cDnypXV15A/A7VfVHSb4BHEyyE3gVeBCgqk4kOQg8D1wAdlfVxfZcDwOPATcDh9tNkjQhC4Z+VX0beN+I+neAe+c4Zi+wd0R9Brhr8dOUJC0HP5ErSR0x9CWpI4a+JHXE0Jekjhj6ktQRQ1+SOmLoS1JHDH1J6oihL0kdMfQlqSOGviR1xNCXpI4Y+pLUEUNfkjpi6EtSRwx9SerIuD+Mrs5s3PPU1Z6CpCvAM31J6ohn+loWc/3L4JV99094JpLm45m+JHXE0Jekjhj6ktQRQ1+SOmLoS1JHxg79JKuS/GmSL7f7tyY5kuSltr1laOwjSU4leTHJfUP1u5M81x77dJIsbzuSpPks5kz/48DJoft7gKNVtRk42u6T5A5gO3AnsBX4TJJV7ZhHgV3A5nbb+pZmL0lalLFCP8l64H7gs0PlbcCBtn8AeGCo/kRVvVFVLwOngC1J1gKrq+pYVRXw+NAxkqQJGPdM/78AvwB8f6h2e1WdA2jbNa2+Djg9NO5Mq61r+5fWL5NkV5KZJDOzs7NjTlGStJAFQz/Jh4HzVfXMmM85ap2+5qlfXqzaX1XTVTU9NTU15h8rSVrIOF/D8EHgI0n+NfB2YHWS/wG8lmRtVZ1rSzfn2/gzwIah49cDZ1t9/Yi6JGlCFjzTr6pHqmp9VW1k8Abt/6yqfw8cAna0YTuAJ9v+IWB7kpuSbGLwhu3xtgT0epJ72lU7Dw0dI0magLfyhWv7gINJdgKvAg8CVNWJJAeB54ELwO6qutiOeRh4DLgZONxukqQJWVToV9XXgK+1/e8A984xbi+wd0R9BrhrsZOUJC0PP5ErSR0x9CWpI4a+JHXE0Jekjhj6ktQRQ1+SOmLoS1JHDH1J6oihL0kdMfQlqSOGviR1xNCXpI4Y+pLUEUNfkjpi6EtSRwx9SeqIoS9JHTH0Jakjhr4kdcTQl6SOGPqS1BFDX5I6YuhLUkcWDP0kb09yPMm3kpxI8mutfmuSI0leattbho55JMmpJC8muW+ofneS59pjn06SK9OWJGmUcc703wD+ZVW9D3g/sDXJPcAe4GhVbQaOtvskuQPYDtwJbAU+k2RVe65HgV3A5nbbunytSJIWsmDo18D32t0b262AbcCBVj8APND2twFPVNUbVfUycArYkmQtsLqqjlVVAY8PHSNJmoCx1vSTrEryLHAeOFJVTwO3V9U5gLZd04avA04PHX6m1da1/UvrkqQJGSv0q+piVb0fWM/grP2ueYaPWqeveeqXP0GyK8lMkpnZ2dlxpihJGsOirt6pqu8CX2OwFv9aW7Khbc+3YWeADUOHrQfOtvr6EfVRf87+qpququmpqanFTFGSNI9xrt6ZSvLutn8z8OPAC8AhYEcbtgN4su0fArYnuSnJJgZv2B5vS0CvJ7mnXbXz0NAxkqQJuGGMMWuBA+0KnLcBB6vqy0mOAQeT7AReBR4EqKoTSQ4CzwMXgN1VdbE918PAY8DNwOF2kyRNyIKhX1V/BnxgRP07wL1zHLMX2DuiPgPM936AJOkK8hO5ktQRQ1+SOmLoS1JHDH1J6oihL0kdGeeSTV3HNu556mpPQdIEeaYvSR3xTF9X1Fz/knhl3/0Tnokk8Exfkrpi6EtSRwx9SeqIoS9JHTH0Jakjhr4kdcTQl6SOGPqS1BFDX5I6YuhLUkcMfUnqiKEvSR0x9CWpI4a+JHXE0Jekjhj6ktSRBX9EJckG4HHgHwDfB/ZX1aeS3Ar8HrAReAX4iar6m3bMI8BO4CLwsar6SqvfDTwG3Az8IfDxqqrlbUmj+LOIkmC8X866APx8VX0zybuAZ5IcAf4DcLSq9iXZA+wBPpHkDmA7cCfwHuCPk/xwVV0EHgV2AV9nEPpbgcPL3ZSuff6ilnR1LLi8U1Xnquqbbf914CSwDtgGHGjDDgAPtP1twBNV9UZVvQycArYkWQusrqpj7ez+8aFjJEkTsKg1/SQbgQ8ATwO3V9U5GLwwAGvasHXA6aHDzrTaurZ/aX3Un7MryUySmdnZ2cVMUZI0j7FDP8k7gS8AP1dVfzvf0BG1mqd+ebFqf1VNV9X01NTUuFOUJC1grNBPciODwP98VX2xlV9rSza07flWPwNsGDp8PXC21dePqEuSJmTB0E8S4HPAyar6jaGHDgE72v4O4Mmh+vYkNyXZBGwGjrcloNeT3NOe86GhYyRJEzDO1TsfBH4KeC7Js632i8A+4GCSncCrwIMAVXUiyUHgeQZX/uxuV+4APMybl2wexit3JGmiFgz9qvrfjF6PB7h3jmP2AntH1GeAuxYzQUnS8vETuZLUEUNfkjpi6EtSRwx9SeqIoS9JHTH0Jakjhr4kdcTQl6SOGPqS1BFDX5I6YuhLUkcMfUnqiKEvSR0x9CWpI4a+JHXE0Jekjhj6ktQRQ1+SOmLoS1JHDH1J6oihL0kdMfQlqSOGviR1xNCXpI7csNCAJL8FfBg4X1V3tdqtwO8BG4FXgJ+oqr9pjz0C7AQuAh+rqq+0+t3AY8DNwB8CH6+qWt52tHHPU1d7CpKuYQuGPoOg/m/A40O1PcDRqtqXZE+7/4kkdwDbgTuB9wB/nOSHq+oi8CiwC/g6g9DfChxerkZ0fZjvReuVffdPcCbS9WnB5Z2q+hPgry8pbwMOtP0DwAND9Seq6o2qehk4BWxJshZYXVXH2tn940PHSJImZKlr+rdX1TmAtl3T6uuA00PjzrTaurZ/aX2kJLuSzCSZmZ2dXeIUJUmXWu43cjOiVvPUR6qq/VU1XVXTU1NTyzY5SerdUkP/tbZkQ9ueb/UzwIahceuBs62+fkRdkjRBSw39Q8COtr8DeHKovj3JTUk2AZuB420J6PUk9yQJ8NDQMZKkCRnnks3fBf45cFuSM8CvAvuAg0l2Aq8CDwJU1YkkB4HngQvA7nblDsDDvHnJ5mG8ckeSJm7B0K+qfzfHQ/fOMX4vsHdEfQa4a1GzkyQtKz+RK0kdMfQlqSOGviR1xNCXpI4Y+pLUEUNfkjoyzrdsSteEub6B02/flMZn6K9Qfm++pKVweUeSOmLoS1JHDH1J6oihL0kdMfQlqSNevfMWTOISQq/SWZiXckrj80xfkjrimb60AP8loeuJoS8tkS8GWolc3pGkjnimr+uWZ+LS5Qz9MSz2ChrDZmXySin1wNC/Rhg4kibB0J8gg13S1Wboqzu++Kpnhv4Qw0DS9W7ioZ9kK/ApYBXw2arad6X+LENcV4Nv5OtaNtHQT7IK+O/AvwLOAN9Icqiqnp/kPKSrYTlPQuZ6AfEFRwuZ9Jn+FuBUVX0bIMkTwDbA0JcWYbkuI77SrsUXp8X+t7jeXjAnHfrrgNND988A/+TSQUl2Abva3e8leXECc1sutwF/dbUnMSG99NpLn7DMveaTV3b8WzB2nxOc03L7h6OKkw79jKjVZYWq/cD+Kz+d5Zdkpqqmr/Y8JqGXXnvpE/rptZc+R5n0d++cATYM3V8PnJ3wHCSpW5MO/W8Am5NsSvJDwHbg0ITnIEndmujyTlVdSPIzwFcYXLL5W1V1YpJzmIAVuSy1RL302kuf0E+vvfR5mVRdtqQuSbpO+X36ktQRQ1+SOmLoL0KSDUn+V5KTSU4k+Xirvy/JsSTPJfmDJKuHjnkkyakkLya57+rNfnGSvD3J8STfar3+WqvfmuRIkpfa9pahY1Zcr/P0+WC7//0k05ccs+L6hHl7/c9JXkjyZ0m+lOTdQ8esuF7n6fPXW4/PJvlqkvcMHbPi+lyyqvI25g1YC/xo238X8BfAHQyuSvpnrf5R4Nfb/h3At4CbgE3A/wFWXe0+xuw1wDvb/o3A08A9wH8C9rT6HuCTK7nXefr8x8B7ga8B00PjV2SfC/T6IeCGVv/kdfx3unpozMeA31zJfS715pn+IlTVuar6Ztt/HTjJ4FPG7wX+pA07Avzbtr8NeKKq3qiql4FTDL6K4ppXA99rd29st2LQ04FWPwA80PZXZK9z9VlVJ6tq1CfBV2SfMG+vX62qC63+dQafn4EV2us8ff7t0LB38OYHQ1dkn0tl6C9Rko3ABxicRfw58JH20IO8+QG0UV87sW5CU3zLkqxK8ixwHjhSVU8Dt1fVORi8CAJr2vAV2+scfc5lxfYJY/X6UeBw21+xvc7VZ5K9SU4DPwn8Shu+YvtcCkN/CZK8E/gC8HPt7OGjwO4kzzBY9vn7HwwdcfiKuUa2qi5W1fsZnPltSXLXPMNXbK+99Anz95rkl4ALwOd/UBr1FFd8kstgrj6r6peqagODHn+mDV+xfS6Fob9ISW5kEPifr6ovAlTVC1X1oaq6G/hdBmuCcJ187URVfZfB2vZW4LUkawHa9nwbtuJ7vaTPuaz4PuHyXpPsAD4M/GS1hW6ug17n+Tv9Hd5chl3xfS6Gob8ISQJ8DjhZVb8xVF/Ttm8Dfhn4zfbQIWB7kpuSbAI2A8cnO+ulSTL1g6s4ktwM/DjwAoOedrRhO4An2/6K7HWePueyIvuEuXvN4IeNPgF8pKr+buiQFdnrPH1uHhr2Ed78e16RfS6VP5e4OB8Efgp4rq0XAvwig+8T2t3ufxH4bYCqOpHkIIPfC7gA7K6qi5Od8pKtBQ5k8MM3bwMOVtWXkxwDDibZCbzK4D2MldzrXH3+G+C/AlPAU0merar7VnCfMHevpxhcuXJkcF7D16vqp1dwr3P1+YUk7wW+D/wl8NOwov/fXRK/hkGSOuLyjiR1xNCXpI4Y+pLUEUNfkjpi6EtSRwx9SeqIoS9JHfn/kdCh5Gvp0QMAAAAASUVORK5CYII=\n"
     },
     "metadata": {
      "needs_background": "light"
     }
    }
   ],
   "source": [
    "res = s.mask((s>s.quantile(0.99))|(s<s.quantile(0.01)))\n",
    "_ = plt.hist(res, bins=50)"
   ]
  },
  {
   "source": [
    "#### 3.\n",
    "(a)"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "Datetime\n",
       "2016-12-31 23:45:04    0.328574\n",
       "2016-12-31 23:50:03    0.261883\n",
       "2016-12-31 23:55:01    0.262406\n",
       "dtype: float64"
      ]
     },
     "metadata": {},
     "execution_count": 83
    }
   ],
   "source": [
    "res = df.Radiation.rolling('6H').corr(df.Temperature)\n",
    "res.tail(3)"
   ]
  },
  {
   "source": [
    "(b)"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "Datetime\n",
       "2016-08-31 21:00:00    51.218750\n",
       "2016-09-01 03:00:00    50.033333\n",
       "2016-09-01 09:00:00    59.379310\n",
       "Freq: 6H, Name: Temperature, dtype: float64"
      ]
     },
     "metadata": {},
     "execution_count": 84
    }
   ],
   "source": [
    "res = df.Temperature.resample('6H', origin='03:00:00').mean()\n",
    "res.head(3)"
   ]
  },
  {
   "source": [
    "(c)"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "Datetime\n",
       "2016-12-31 17:45:02    9.33\n",
       "2016-12-31 17:50:01    8.49\n",
       "2016-12-31 17:55:02    5.84\n",
       "Name: Radiation, dtype: float64"
      ]
     },
     "metadata": {},
     "execution_count": 85
    }
   ],
   "source": [
    "my_dt = df.index.shift(freq='-6H')\n",
    "int_loc = [df.index.get_loc(i, method='nearest') for i in my_dt]\n",
    "res = df.Radiation.iloc[int_loc]\n",
    "res.tail(3)"
   ]
  },
  {
   "source": [
    "### Ex2：水果销量数据集\n",
    "#### 1.\n",
    "(a)"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "source": [
    "df = pd.read_csv('../data/fruit.csv')\n",
    "df.Date = pd.to_datetime(df.Date)\n",
    "df_grape = df.query(\"Fruit == 'Grape'\")\n",
    "res = df_grape.groupby([np.where(df_grape.Date.dt.day<=15,'First', 'Second'),\n",
    "                        df_grape.Date.dt.month])['Sale'].mean().to_frame().unstack(0).droplevel(0,axis=1)\n",
    "res = (res.First/res.Second).rename_axis('Month')\n",
    "res.head()"
   ],
   "cell_type": "code",
   "metadata": {},
   "execution_count": 86,
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "Month\n",
       "1    1.174998\n",
       "2    0.968890\n",
       "3    0.951351\n",
       "4    1.020797\n",
       "5    0.931061\n",
       "dtype: float64"
      ]
     },
     "metadata": {},
     "execution_count": 86
    }
   ]
  },
  {
   "source": [
    "(b)"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "Date\n",
       "2019-01-31    847\n",
       "2019-02-28    774\n",
       "2019-03-31    761\n",
       "2019-04-30    648\n",
       "2019-05-31    616\n",
       "Name: Sale, dtype: int64"
      ]
     },
     "metadata": {},
     "execution_count": 87
    }
   ],
   "source": [
    "df[df.Date.dt.is_month_end].query(\"Fruit == 'Pear'\").groupby('Date').Sale.sum().head()"
   ]
  },
  {
   "source": [
    "(c)"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "Date\n",
       "2019-01-31    60.500000\n",
       "2019-02-28    59.538462\n",
       "2019-03-29    56.666667\n",
       "2019-04-30    64.800000\n",
       "2019-05-31    61.600000\n",
       "Name: Sale, dtype: float64"
      ]
     },
     "metadata": {},
     "execution_count": 88
    }
   ],
   "source": [
    "df[df.Date.isin(pd.date_range('20190101', '20191231',freq='BM'))].query(\"Fruit == 'Pear'\").groupby('Date').Sale.mean().head()"
   ]
  },
  {
   "source": [
    "(d)"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "Month\n",
       "1    65.313725\n",
       "2    54.061538\n",
       "3    59.325581\n",
       "4    65.795455\n",
       "5    57.465116\n",
       "Name: Sale, dtype: float64"
      ]
     },
     "metadata": {},
     "execution_count": 89
    }
   ],
   "source": [
    "target_dt = df.drop_duplicates().groupby(df.Date.drop_duplicates().dt.month)['Date'].nlargest(5).reset_index(drop=True)\n",
    "res = df.set_index('Date').loc[target_dt].reset_index().query(\"Fruit == 'Apple'\")\n",
    "res = res.groupby(res.Date.dt.month)['Sale'].mean().rename_axis('Month')\n",
    "res.head()"
   ]
  },
  {
   "source": [
    "#### 2."
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "Date        January  February  March  April  May  June  July  August  \\\n",
       "Fruit Date                                                             \n",
       "Apple Mon        46        43     43     47   43    40    41      38   \n",
       "      Tue        50        40     44     52   46    39    50      42   \n",
       "      Wed        50        47     37     43   39    39    58      43   \n",
       "      Thu        45        35     31     47   58    33    52      44   \n",
       "      Fri        32        33     52     31   46    38    37      48   \n",
       "\n",
       "Date        September  October  November  December  \n",
       "Fruit Date                                          \n",
       "Apple Mon          59       42        39        45  \n",
       "      Tue          40       57        47        47  \n",
       "      Wed          35       46        47        38  \n",
       "      Thu          36       63        37        40  \n",
       "      Fri          34       37        46        41  "
      ],
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>Date</th>\n      <th>January</th>\n      <th>February</th>\n      <th>March</th>\n      <th>April</th>\n      <th>May</th>\n      <th>June</th>\n      <th>July</th>\n      <th>August</th>\n      <th>September</th>\n      <th>October</th>\n      <th>November</th>\n      <th>December</th>\n    </tr>\n    <tr>\n      <th>Fruit</th>\n      <th>Date</th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th rowspan=\"5\" valign=\"top\">Apple</th>\n      <th>Mon</th>\n      <td>46</td>\n      <td>43</td>\n      <td>43</td>\n      <td>47</td>\n      <td>43</td>\n      <td>40</td>\n      <td>41</td>\n      <td>38</td>\n      <td>59</td>\n      <td>42</td>\n      <td>39</td>\n      <td>45</td>\n    </tr>\n    <tr>\n      <th>Tue</th>\n      <td>50</td>\n      <td>40</td>\n      <td>44</td>\n      <td>52</td>\n      <td>46</td>\n      <td>39</td>\n      <td>50</td>\n      <td>42</td>\n      <td>40</td>\n      <td>57</td>\n      <td>47</td>\n      <td>47</td>\n    </tr>\n    <tr>\n      <th>Wed</th>\n      <td>50</td>\n      <td>47</td>\n      <td>37</td>\n      <td>43</td>\n      <td>39</td>\n      <td>39</td>\n      <td>58</td>\n      <td>43</td>\n      <td>35</td>\n      <td>46</td>\n      <td>47</td>\n      <td>38</td>\n    </tr>\n    <tr>\n      <th>Thu</th>\n      <td>45</td>\n      <td>35</td>\n      <td>31</td>\n      <td>47</td>\n      <td>58</td>\n      <td>33</td>\n      <td>52</td>\n      <td>44</td>\n      <td>36</td>\n      <td>63</td>\n      <td>37</td>\n      <td>40</td>\n    </tr>\n    <tr>\n      <th>Fri</th>\n      <td>32</td>\n      <td>33</td>\n      <td>52</td>\n      <td>31</td>\n      <td>46</td>\n      <td>38</td>\n      <td>37</td>\n      <td>48</td>\n      <td>34</td>\n      <td>37</td>\n      <td>46</td>\n      <td>41</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 90
    }
   ],
   "source": [
    "month_order = ['January','February','March','April','May','June','July','August','September','October','November','December']\n",
    "week_order = ['Mon','Tue','Wed','Thu','Fri','Sat','Sum']\n",
    "group1 = df.Date.dt.month_name().astype('category').cat.reorder_categories(month_order, ordered=True)\n",
    "group2 = df.Fruit\n",
    "group3 = df.Date.dt.dayofweek.replace(dict(zip(range(7),week_order))).astype('category').cat.reorder_categories(week_order, ordered=True)\n",
    "res = df.groupby([group1, group2,group3])['Sale'].count().to_frame().unstack(0).droplevel(0,axis=1)\n",
    "res.head()"
   ]
  },
  {
   "source": [
    "#### 3."
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "2019-01-01    189.000000\n",
       "2019-01-02    335.500000\n",
       "2019-01-03    520.333333\n",
       "2019-01-04    527.750000\n",
       "2019-01-05    527.750000\n",
       "Freq: D, dtype: float64"
      ]
     },
     "metadata": {},
     "execution_count": 91
    }
   ],
   "source": [
    "df_apple = df[(df.Fruit=='Apple')&(~df.Date.dt.dayofweek.isin([5,6]))]\n",
    "s = pd.Series(df_apple.Sale.values,index=df_apple.Date).groupby('Date').sum()\n",
    "res = s.rolling('10D').mean().reindex(pd.date_range('20190101','20191231')).fillna(method='ffill')\n",
    "res.head()"
   ]
  }
 ]
}